Joe Joe -4 years ago 180
SQL Question

Pentaho Spoon Transformation - Errors

I have been trying to run a transformation where I have Table Input with SQL to fetch or query data, tied to output for an excel spreadsheet.

With the following SQL I get multiple errors depending on what I add or take away. I am connected to Oracle databases.


WHERE ((View.Opened_Date

BETWEEN ' 2016-01-01'

AND ' 2017-01-01'

AND View.Owner_Group='OwnerJ'

AND View.Main_Category1 LIKE '%BUCKETS%'))

I get the following error when trying to preview:

Transformation detected one or more steps with errors.

Goes on to say its killed all other steps. No long that I can find. Earlier when I ran the SQL I got date errors. Any suggestions would be helpful.

Answer Source

You don't seem to be selecting any columns from the table.
There are leading spaces in the dates and you should apply TO_DATE to the dates that are in string format.
There are unnecessary parentheses that could be removed.
Also, I think that '.View' should be just 'View'.

SELECT column1Name, column2Name, ... FROM View 

WHERE View.Opened_Date 

BETWEEN TO_DATE('2016-01-01', 'yyyy-mm-dd') 

AND TO_DATE('2017-01-01' , 'yyyy-mm-dd')

AND View.Owner_Group='OwnerJ' 

AND View.Main_Category1 LIKE '%BUCKETS%'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download