Conditional SQL Joins between 2 tables in a universe

I have a situation where I have 2 tables, one is LOAN and the other is DLRY_ED_RSLT where LOAN has a one to many relationship to DLRY_ED_RSLT. The keys are LOAN.LN_DLRY_SYS_SEQ_NO and DLRY_ED_RSLT.ED_KEY_ID and the DLRY_ED_RSLT.ED_KEY_ID can either be the LOAN.LN_DLRY_SYS_SEQ_NO or a combination of 3 columns in the LOAN table consisting of Pool ID + Suffix + Seller ID, literally they put plus signs between each column value and concatenated them. This logic below works in the edit join property box of the link between both tables, but is not conditional. I need to code it such as if LOAN.LN_DLRY_SYS_SEQ_NO does not equal DLRY_ED_RSLT.ED_KEY_ID then join on the combination of the 3 columns. Does anyone have any suggestions?? :crazy_face:

my current code:
to_char(LOAN.LN_DLRY_SYS_SEQ_NO)=DLRY_ED_RSLT.ED_KEY_ID OR to_char(LOAN.MBS_POOL_ID)||’+’||rtrim(LOAN.MBS_POOL_SFX_ID)||’+’||to_char(LOAN.SELR_LNDR_ID)=DLRY_ED_RSLT.ED_KEY_ID

[b]Sample Data
LN_DLRY_SYS_SEQ_NO
3653
3654

ED_KEY_ID
3653 --------This would match LN_DLRY_SYS_SEQ_NO
54656+250+9632100 ---------This is the combination of the 3 columns. [/b]

Thanks,
-Will


Will :us: (BOB member since 2004-06-14)

An Option:

You could create an alias of your DLRY_ED_RSLT table,
join the two tables to your LOAN table appropriately
(one based on the straight ID join, the other on the concatenated key)
then include objects for both legs in your universe.


Chris Pohl :us: (BOB member since 2002-06-18)