The Next level of the Transformer

[JDionne]

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

[JDionne]

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

[ray.wurlod]

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

[Teej]

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.