Pentaho

 View Only

 Database lookup and Database join are driving me crazy

  • Pentaho
  • Pentaho
Max Bobzien's profile image
Max Bobzien posted 09-13-2018 03:50

Hi,

I have a transformation where I use a database lookup or database join step to look up a value from my database. The problem is: it does not find the record in my table! I am 100 % sure it is there and if I run the SQL in another editor it returns the result but not from PDI.

When I run the same SQL in a table input step, it returns the correct result.

Is there a way for me to check what SQL exactly the database lookup or database join steps are sending to the server (besides checking on the server itself). If I change the logging to detailed or debugging I just get the SQL statement with the ? but not what is send to the server.

Any ideas how to troubleshoot this?

Max


#Pentaho
Johan Hammink's profile image
Johan Hammink posted 09-13-2018 05:41

Did you use the "Load all data from table" option? See documentation about this step what can be the problem when using this option.

Max Bobzien's profile image
Max Bobzien posted 09-14-2018 22:19

No, did not use that one.

Ricardo Miguel Díaz Razo's profile image
Ricardo Miguel Díaz Razo posted 09-20-2018 17:42

This is a problem of "data types"

Please, can you make a right clic on your two steps (origin and source step ) and select the "Input Fields.." and "Output Fields".

Please see that the column in two steps has the same type a lenght

Alain Debecker's profile image
Alain Debecker posted 09-21-2018 11:24

Ricardo Diaz is correct the issue may come from using different data types.

It may also come from invisible characters (spaces, strange ascii,...).

You need to isolate the guilty row or value and white them in an editor showing all characters. I am using Notepad++ with the option show all characters (Show hexadecimal/ASCII codes on Notepad++), but many other editors are able to show the Hex-values.

Once you know what has to be changed, you may correct it, with a JavaScript for example, or in the SQL the a Table Input [This is one of the rare case where something else than a SELECT * in the SQL of the Table Input does not hurt my eyes]. Just read the data in table, apply your change and use the Stream Lookup or the Join Rows (Cartesian product) (with the join condition). Don't be afraid for performance, those steps optimized for large volume and are pretty fast, sometimes faster that the database lookup.

Related Content