PDA

View Full Version : Parameters in Kettle



kettle_anonymous
03-29-2006, 04:37 PM
I have a dozen table inputs. The inputs will go into 1 output table.

Example:

Fields
#0 Dimension
#1 Product Name
#2 Items
#3 Month
#4 Year

SALES A 5 November 2005
SALES B 6 February 2004
SALES C 3 November 2005
SALES D 2 December 2005
SALES E 3 June 2004

I have dimensions of SALES, ORDERS etc.

What I want to do is have a parametarized SELECT query like this... MONTH = November and YEAR = 2005

MattCasters
03-29-2006, 08:36 PM
My condolences on the data model.

For parameterised Table Input: see the Spoon manual.

Use a format like this:

SELECT f1, f2, f3 FROM t1 WHERE month = ? and year = ?

The ? get replaced by the input from the previous step if you set the correct fields in the Table Input dialog.

HTH,

Matt

kettle_anonymous
03-30-2006, 03:54 AM
What is the correct step that I should use? Where do I declare the '?'s

I've been gaining a lot of knowledge in using Kettle and I feel more comfortable using it now. However, there are still portions I cannot understand.

Thanks! I hope I can help someday.

MattCasters
03-30-2006, 04:01 AM
Where do I declare the '?'s

That's the nice part of it: you decide what to send to these markers.
You can read the parameters from a text-file, a database table, Excel sheet, an argument, etc. Then simply send this information to the Table Input step and specify "Insert data from step".
The first field is the first ?, the second the second ?, etc.

You can even execute the query multiple times: once for every input row the step receives.

Hope this helps,

Matt

smilez2k7
01-16-2008, 12:47 PM
i tried that but got this error

2008/01/16 11:40:18 - dummy.0 - Starting to run...
2008/01/16 11:40:18 - Table input 2.0 - Starting to run...
2008/01/16 11:40:18 - Table input 2.0 - Reading query parameters from stream [get Marker]
2008/01/16 11:40:18 - Table input 2.0 - Reading from step [get Marker]
2008/01/16 11:40:18 - Table input 2.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : Unexpected error :
2008/01/16 11:40:18 - Table input 2.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : java.lang.NullPointerException
2008/01/16 11:40:18 - Table input 2.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.trans.step.BaseStep.getRowFrom(BaseStep.java:1418)
2008/01/16 11:40:18 - Table input 2.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.trans.steps.tableinput.TableInput.readStartDate(TableInput.java:62)
2008/01/16 11:40:18 - Table input 2.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:100)
2008/01/16 11:40:18 - Table input 2.0 - ERROR (version 3.0.0-M2, build 252004 from 2007/09/06 01:03:58) : at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:302)

MattCasters
01-16-2008, 12:55 PM
I would suggest you update your Kettle software to a stable version, for example 3.0.1GA?

HTH,

Matt

sboden
01-16-2008, 12:56 PM
Use at least v3.0.1 ... and be sure you switch on "execute for each row"

Regards,
Sven