Search code examples
sqlpentaho

Database join in pentaho error with the parameters

I have a problem with my SQL script.

I don't know why it throws an error when I do the data_base_join step and I join with the parameter passed from the previous step and I get an error that the parameter is null but when I check the step before there is data!

enter image description here

This is the error I get:

2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - ERROR (version 8.3.0.0-371, build 8.3.0.0-371 from 2019-06-11 11.09.08 by buildguy) : A database error occurred: 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - Couldn't get field info from [set @db = ?
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - select 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - SUM(mo.`status`='OK') AS n_ok
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - from
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - @db.monitoring_pentaho_scripts  mo
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - where mo.`status`='OK'
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - ;
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - ]
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - SUM(mo.`status`='OK') AS n_ok
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - 
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - from
2023/03/24 10:46:09 - org.pentaho.di.trans.steps.databasejoin.DatabaseJoinMeta@4dbcc8ce - @db.monitoring_pentaho_scripts' at line 2
2023/03/24 10:46:09 - get ok satutus .0 - ERROR (version 8.3.0.0-371, build 8.3.0.0-371 from 2019-06-11 11.09.08 by buildguy) : Because of an error, this step can't continue: 
2023/03/24 10:46:09 - get ok satutus .0 - Error obtaining fields for this step
Solution

  • Your SQL request needs some change.

    "SUM" is used with a "GROUP BY", which you have not here in your code. This causes the error message.

    As you already have the filter condition in your where clause I would replace:

    SUM(mo.`status`='OK') AS n_ok

    by

    COUNT(*) AS n_ok