Talend community forum / component for SUB SELECT with IN construct

myklynysh · 2009-07-06 17:51:46

Hello everyone,

I'm new in Talend and need some help.
Which component can I use to create result set like such SQL:

SELECT *
FROM report
WHERE executiontime IN (SELECT MAX(executiontime) FROM report GROUP BY searchjob_id);

The table report looks like that:
searchjob_id search_id executiontime result
1 10 10.10.2008 AAA
1 12 12.10.2008 BBB
2 14 14.12.2008 CCC
2 15 11.12.2008 DDD
3 17 01.10.2008 EEE

As result I want to have somethink like that
searchjob_id search_id executiontime result
1 12 12.10.2008 BBB
2 14 14.12.2008 CCC
3 17 01.10.2008 EEE

The data come not from the table but from other component(tMap).

Thanks in advance
myklynysh

shong · 2009-07-07 07:24:39

Hello

Which component can I use to create result set like such SQL:

tAggregateRow component can fit your need.eg:
...tMap-->tAggregateRow-->

Best regards

shong

myklynysh · 2009-07-07 08:46:35

Thank you for answer, shong.

I tried that. I can set the function for executiontime to MAX, but how can I define association to other record fields like search_id or result?

Which operation should I use? If don't define some operation, I get always 0 as return in these fields.

shong · 2009-07-07 10:35:59

Hello
First, output the result come from tMap to file.
Second, link this file to a tAggregateRow, on tAggregateRow, only define two columns:searchjob_id and executiontime, then do a inner join with the file again based on executiontime column.
---tMap--tFileOutputDelimited
|
OnSubJobOK
|
tFileInputDelimited--tAggregateRow--lookup>tMap-->tLogRow
|
main
|
tFileInputDelimited

Best regards

shong

Announcement

Unanswered posts

#1 2009-07-06 17:51:46

component for SUB SELECT with IN construct

#2 2009-07-07 07:24:39

Re: component for SUB SELECT with IN construct

#3 2009-07-07 08:46:35

Re: component for SUB SELECT with IN construct

#4 2009-07-07 10:35:59

Re: component for SUB SELECT with IN construct

Board footer