Hi all,
First of all thank u for this wonderful platform.
I have a querry.
I am using a transformer to lookup a column from odbc stage.
I have a coumn EFFDT in the input data to the transformer , STARTDATE in lookup odbc and
the condition for lookup EFFDT<STARTDATE.
How can i implement this condition in the keyexpression of refernce link.
please help me out in resolving this issue.
lookup condition
Moderators: chulett, rschirm, roy
Welcome. :D
Your Key Expression would simply be the column EFFDT so that it is passed to the ODBC lookup, everything critical is setup there. I'd suggest you first let the ODBC stage generate the SQL so you can see the syntax and then modify it. It will produce SQL for an equi-join and you'll need to edit it for the "<" part.
Lastly, if this needs to return multiple rows, you'll need to enable the 'Multiple Row Result Set' option on the reference link itself, otherwise all you'll get is one record returned no matter how many the SQL selects.
Your Key Expression would simply be the column EFFDT so that it is passed to the ODBC lookup, everything critical is setup there. I'd suggest you first let the ODBC stage generate the SQL so you can see the syntax and then modify it. It will produce SQL for an equi-join and you'll need to edit it for the "<" part.
Lastly, if this needs to return multiple rows, you'll need to enable the 'Multiple Row Result Set' option on the reference link itself, otherwise all you'll get is one record returned no matter how many the SQL selects.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
[quote="chulett"]Welcome. :D
Your [b]Key Expression[/b] would simply be the column EFFDT so that it is passed to the ODBC lookup, everything critical is setup there. I'd suggest you first let the ODBC stage generate the SQL so you can see the syntax and then modify it. It will produce SQL for an equi-join and you'll need to edit it for the "<" part.
Lastly, if this needs to return multiple rows, you'll need to enable the 'Multiple Row Result Set' option on the reference link itself, otherwise all you'll get is one record returned no matter how many the SQL selects.[/quote]
Hi Chullet,
Thanks a lot.
If iam right i should change the condition( replace STARTDATE=? with STARTDATE>? ) in SQL for reference inputs section in the ODBC stage.
Your [b]Key Expression[/b] would simply be the column EFFDT so that it is passed to the ODBC lookup, everything critical is setup there. I'd suggest you first let the ODBC stage generate the SQL so you can see the syntax and then modify it. It will produce SQL for an equi-join and you'll need to edit it for the "<" part.
Lastly, if this needs to return multiple rows, you'll need to enable the 'Multiple Row Result Set' option on the reference link itself, otherwise all you'll get is one record returned no matter how many the SQL selects.[/quote]
Hi Chullet,
Thanks a lot.
If iam right i should change the condition( replace STARTDATE=? with STARTDATE>? ) in SQL for reference inputs section in the ODBC stage.