Implementing ANSI join condition in Universe

building reports desktopintelligence
1

Hi ,

I have ANSI join condition like below

Select …
FROM
CUS_REF_DATA,
PRO_VC,
PRO_PO left outer join HS_FR_VC ON PRO_PO.SIT_ID IN (HS_FR_VC.SIT_ID , HS_FR_VC.SIT_ID2)
WHERE
( CUS_REF_DATA.GRC=trim(PRO_PO.GR)
and CUS_REF_DATA.MCN=trim(PRO_PO.MC)
and CUS_REF_DATA.SOC=trim(PRO_PO.SO) )
AND ( PRO_PO.SIT_ID=PRO_VC.A_SIT_ID or PRO_PO.SIT_ID=PRO_VC.Z_SIT_ID)

Currently I have checked the “Do not generate SQL before running” box inorder to protect my query… I have no idea on how to implement that ANSI left outer join condition in my designer universe.

BO version - 6.5.1 — Oracle 9i version

Can someone suggest me in detail how to implement this logic in Designer.

Thanks in Advance

Canton (BOB member since 2006-10-16)

2

In your universe, change the ANSI92-parameter (File, Parameters…, tab “Parameter”) to Yes. This will make the joins in your SQL ANSI-style.

jobjoris :netherlands: (BOB member since 2004-06-30)

3

Hi Job,
Thanks for ur comments.

I want to know how to implement the below join condition.
How do I make the left outer join in the universe while I am in the join dialog box, like the below one.

PRO_PO left outer join HS_FR_VC ON PRO_PO.SIT_ID IN (HS_FR_VC.SIT_ID , HS_FR_VC.SIT_ID2)

I set the parameter ANSI92 to Yes…then I went to Join dialog box, when I made the join PRO_PO.SIT_ID with HS_FR_VC.SIT_ID , HS_FR_VC.SIT_ID2 its taking as between in the expression box.

Im gettinng expression like this “PROVG_PORT.SITE_ID between HSPS_FRS_PVC.SITE_ID and HSPS_FRS_PVC.SITE_ID2”

But I dont want this way of generation… can you explain in detail on the steps to follow, even I checke with the Help document…There is no enough information.

Canton (BOB member since 2006-10-16)