Pentaho Kettle Spoon Date manipulation

Question

I am using Pentaho Spoon to do some transformation. I am using 'Table Input' and joining multiple tables to get final output table.

I need to achieve:

SELECT COUNT(distinct ID)
FROM TBLA join TBLB ON TBLA.ID=TBLB.ID
WHERE
  TBLA.ID=334
  AND TBLA.date = '2013-1-9'
  AND TBLB.date BETWEEN '2012-11-15' AND '2013-1-9';

I am manually inserting '2012-11-15' but I am using Get System Data to insert '2012-1-9'. I am using 1 Get System Data.

My query is:

SELECT COUNT(distinct ID)
FROM TBLA join TBLB ON TBLA.ID=TBLB.ID
WHERE
  TBLA.ID=334
  AND TBLA.date='?'
  AND TBLB.date BETWEEN '2012-11-15' AND '?';

I get error message in Table Input saying No value specified for parameter 2

Any suggestion will be appreciated.

Thank you.

Codek Codek · Accepted Answer · 2013-01-11T09:06:05

Simple one this; You need to "duplicate" the system date. So add another line in "get system data" called "date2" or something, make it the same as the first line, and then it will fill in the 2nd parameter or ?

OR simply change the query to say between '2012-11-15' and TBLA.date then you dont need the 2nd parameter

Pentaho Kettle Spoon Date manipulation

2 Answers