PDA

View Full Version : Transformation Arguments



shassan2
04-25-2006, 09:26 AM
Attachment: Sans titre.JPG (http://forums.pentaho.org/archived_att/kettle/Sans titre.JPG) Hello,


I have one transformation TRANS A with
a Table Input
SELECT
case count(*)
WHEN 0 THEN 2
ELSE count(*)
END AS nr
FROM T_Trans_Log
WHERE
ID_BATCH=(SELECT MAX(ID_BATCH)
FROM T_Trans_Log
WHERE TRANSNAME='?')
AND TRANSNAME='?';



? is the name of one transformation TRANS B passed in arguments



I have tried to pass this argument (name of transformation) in Fields Arguments ("Transformation Step" in Chef)..Please see attached image..but i can success.



The idea is to pass a transformation Name to TRANS B which retour the result of the previous query for this transformation (sorry..a lot of transformation in my words :)






An idea ?



Thanks

MattCasters
04-25-2006, 02:00 PM
The arguments you specify can be obtained with the "Get System Info Step".
These arguments can then be passed to a Table Input step.
You need to loose the quotes (') around the ? though.

All the best,

Matt

shassan2
04-25-2006, 11:47 PM
Hi matt,

Ok..But which information in "Get Systme Info Step" do i use?


Thanks

MattCasters
04-26-2006, 12:11 AM
Euuh, how about "Command line argument 1" etc?

;-)

Matt

shassan2
04-26-2006, 04:10 AM
Good..It works..Thanks a lot

I can now check if the trans is still working before running it...

dyutiman
05-07-2008, 07:28 AM
Hi
In my transformation after one insert/update I am getting time_id [with all other fields]. To remove all the extra fields I added one "select value" and only getting the time_id. After that I used "unique rows" to get unique time_id.
Now I tried to get data from a table, so I used "table input" after "unique rows". So I should get only time_id as the argument to my "table input". I selected that step as the "insert data from step", but its not working, the hops is getting yellow.

I searched forum and found that "table input" only takes arguments from "get system info". So I added it after my "unique rows", and in "get system info" I selected "command line argument 1". But still the "table input" not taking the argument and the hops is still yellow [i changed that "insert data from step" to "get system info".

When I try to select the "show input fields" on "table output" it says "I couldn't find any fields".
Please help.

Thanks in advance

DEinspanjer
05-07-2008, 11:32 AM
There is nothing about Table Input only being able to read from Get System Information.
You were close to the right solution with your first try. There was probably just something simple left out.
Any chance you could attach the transformation or at least screen shot the table input dialog?

dyutiman
05-08-2008, 05:40 AM
Hi
Here is the transformation file attached for your reference.

dyutiman
05-08-2008, 05:44 AM
And here is the snippet of the error I am getting :

2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException:
2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)]
2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [time_id_1_1_1 Integer(10)] on prepared statement (Integer)
2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Parameter index out of range (2 > number of parameters, which is 1).
2008/05/08 14:55:35 - Kettle - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : ERROR in part: P Set values
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Unexpected error :
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : org.pentaho.di.core.exception.KettleDatabaseException:
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : An error occurred executing SQL in part [P Set values]:
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : select
datetime .start_day,

time_id as id_of_time,

account_id as id_of_account ,

sum(Clicks) as 'Total Clicks',

sum(Imps) as 'Total Impressions',

sum(sales_count) as 'Purchases',

sum(lead_count) as 'Leads',

sum(signup_count) as 'Signups',

round(sum(Cost)/1000000,2) as 'Total Cost',

sum(Conv) as 'Total Conversions',

round(sum(Clicks)*100/sum(Imps),2) as 'Click Through Rate',

round(sum(Cost)/(sum(Clicks)*1000000),2) as 'Avg CPC',

round(sum(Conv)*100/sum(Clicks),2) as 'Conversion Rate',

round(sum(Cost)/(sum(Conv)*1000000),2) as 'Cost Per Conversion',

round(avg(pos),2) as 'Average Position'

from fact,datetime where fact.time_id=datetime.id and datetime.id = ?

GROUP BY account_id,time_id;
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : offending row : [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)], [time_id_1_1_1 Integer(10)]
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) :
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Error setting value #2 [time_id_1_1_1 Integer(10)] on prepared statement (Integer)
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).
2008/05/08 14:55:35 - Read from fact table.0 - ERROR (version 3.0.1, build 534 from 2007/12/12 12:28:23) : Parameter index out of range (2 > number of parameters, which is 1).

sboden
05-08-2008, 06:32 AM
Don't hi-jack threads :D ... in your case the problem is easy "2 > number of parameters, which is 1" ... you have more than 1 field in the row coming into table input.

Use the "insert data from step" and switch on "execute for each row".

Regards,
Sven

dyutiman
05-15-2008, 06:13 AM
thanks
That really worked. Actually I got confused about that yellow hops and when I right clicked the table input and tried to view the input data, it was showing nothing.
Anyway, now its working.