cancel
Showing results for 
Search instead for 
Did you mean: 

Need to join to two tables based on if condition

0 Kudos

we are trying to join two tables based on below condition

if TE_TM_ENTRY_MO.RSRC_TP='EMPLOYEE'

then TE_TM_ENTRY_MO should be joined with LBR_PERSON_MSTR bsed on the condition "TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.MYHR_ID

else if TE_TM_ENTRY_MO.RSRC_TP<>'EMPLOYEE'

then TE_TM_ENTRY_MO should be joined with LBR_PERSON_MSTR based on the condition "TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.NUID"

Please let me know how to implement in BO Universe.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

I'd imagine something like this would work:

(TE_TM_ENTRY_MO.RSRC_TP='EMPLOYEE' AND TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.MYHR_ID) OR (TE_TM_ENTRY_MO.RSRC_TP != 'EMPLOYEE' AND TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.NUID)

Answers (4)

Answers (4)

0 Kudos

Thanks all for your response.

Former Member
0 Kudos

You can always create a complex join on universe by
using following steps:

1) Join botht the tables by using following condition:
TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.MYHR_ID

2) Now you can edit the join by double clicking the join link which
is created in previous step
like

Case when TE_TM_ENTRY_MO.RSRC_TP='EMPLOYEE' then
TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.MYHR_ID
else TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.NUID end

I hope this will help you.

Thanks,
Swapnil

former_member285534
Active Participant
0 Kudos

Hi Sasi,

Try this join condition

TE_TM_ENTRY_MO.EMP_ID = ( CASE WHEN TE_TM_ENTRY_MO.RSRC_TP='EMPLOYEE' THENLBR_PERSON_MSTR.MYHR_ID ELSE LBR_PERSON_MSTR.NUID END )

Regards,

Fernando

Former Member
0 Kudos

In Data Foundation Layer :

>>You can create alias table for "LBR_PERSON_MSTR"

>>then you can join "LBR_PERSON_MSTR" table with MYHR_ID and Alias of "LBR_PERSON_MSTR" table with  one more with MSTR.NUID.

>>When you creating join between those tables have to add the condition for one join "TE_TM_ENTRY_MO.RSRC_TP='EMPLOYEE" for another join "TE_TM_ENTRY_MO.RSRC_TP<>'EMPLOYEE'"

Example :

"TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.MYHR_ID and  TE_TM_ENTRY_MO.RSRC_TP='EMPLOYEE' and Validate this Expression.

In Business Layer:

>>Create two objects one from this join("TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.MYHR_ID") one from alias join("TE_TM_ENTRY_MO.EMP_ID=LBR_PERSON_MSTR.NUID")

In Report Level:

Option 1:You can use combine query if you doesn't have any performance issues (Depends on your requirement use Union (or) any other which can fulfill your requirement   )

Option 2:Take two data provides and Merge them (Use merge dimension)