I have a need to do some fairly simple SQL code in a transformer. This is the bit of code that im confused at:
FROM [DM_JOC_AGGR_YR_MTH_TBL] f, [REF_JOC_VARIABLES_LU_TBL] v
WHERE f.direction_id = v.direction_id
and VDATE_MONTH_ID > v.Rolling_12_Cutoff
and the bit that gets me si the > rolling_12_cutoff. How do I do a conditional Join like this in DS?
Jim
The Next level of the Transformer
Moderators: chulett, rschirm, roy
The Next level of the Transformer
Sure I need help....But who dosent?
A poster replying to a diff post just gave me the solution:
3) No , the usual advise is to preload your lookup data to a hash file and do the lookups from there. Since I don't like hash files that much, I usually move the "lookup table" to the database where the main query is executing in (if it's not already the same database) and do the lookup in the input SQL statement via joins.
Ogmios
Thats what im gona do. Thanx Ogmios!!
Jim
3) No , the usual advise is to preload your lookup data to a hash file and do the lookups from there. Since I don't like hash files that much, I usually move the "lookup table" to the database where the main query is executing in (if it's not already the same database) and do the lookup in the input SQL statement via joins.
Ogmios
Thats what im gona do. Thanx Ogmios!!
Jim
Sure I need help....But who dosent?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If the two tables/views are in the same database, you can also use the passive stage that extracts them.
When building the SQL, add the join condition to the Selection criteria, and inspect the generated SQL. If it's not quite right, change to user-defined SQL and edit what was generated. :D
When building the SQL, add the join condition to the Selection criteria, and inspect the generated SQL. If it's not quite right, change to user-defined SQL and edit what was generated. :D
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
On the mailing list, there was a similar post asking about conditional joins.
This is something that should be looked on as a potential feature to request Ascential to make. It would address the shortcoming of the Full Outer Join -- "If right is null, use left! If Left is null, use right! If the world is null, hope you don't forget your towel."
-T.J.
This is something that should be looked on as a potential feature to request Ascential to make. It would address the shortcoming of the Full Outer Join -- "If right is null, use left! If Left is null, use right! If the world is null, hope you don't forget your towel."
-T.J.
Developer of DataStage Parallel Engine (Orchestrate).