How can i get a resultset from a select-sql in Pentaho-Kettle?

Question

I dont' know how I can deal with the expected resultset derived from a select-based SQL query launched on database table.

I've created a Transformation which includes a Execute SQL Script where I've hardcoded the intended select-sql query. By previewing the transformation I get an empty set of rows, in spite of knowing that sql query does actually recover a non empty set of rows.

I'd expect a non empty result set, that is, after clicking 'preview...' menu item, I'd expect a table showing all the matching rows of the targeted table.

UPDATE: The select-sql query I'm launching on the database is:

SELECT 'BATC001a' AS QAcheck, 'description' AS QADescription, "tblBAS".*, "tblLTFU"."D_Date"
FROM "table1" INNER JOIN "table2" ON "table1"."PATIENT" = "table2"."PATIENT" 
WHERE ("table2"."D_Date" > "table1"."B_Date") AND NOT ("D_Date" BETWEEN '1911-11-11 00:00:00' AND '1911-11-11 23:59:59')

The sql works fine because if it's placed inside a Table Input step the non-empty expected resultset is retrived, as suggested in a post.

Mind sharing the Query in question ? Helps a lot. There could be a clause in there that doesn't work with Pentaho. — Cristian Curti, May 24 '19 at 15:52

score 2 · Accepted Answer · answered May 24 '19 at 16:08

2

Don’t use SQL script. Use Table input instead.

answered May 24 '19 at 16:08

nsousa

4,448
1
10
15

Thanks. It worked with the step proposed. Now I have other problem but I'll explained it on a different post. – txapeldot May 25 '19 at 13:49

How can i get a resultset from a select-sql in Pentaho-Kettle?

1 Answers1