Self restricting outer join when ANSI92 is enabled

building semantic layer
1

Hi,

Can someone help me how to solve this in BO 6.5.1

Designer is set to use ANSI92 = yes

Scenario: I have a table which has a self join. But i donot want the query to fail if it doesnot find a match on the value in the join and so i make it as an outer join. But BO doesnot allow me to use this kind of a join. It is showing an error Exception: DBD, ORA-25156: old style outer join (+) cannot be used with ANSI joins
State: N/A

The join is as follows: T1.OCCUPATION_CODE(+) = ‘CODE3’

Since it is ANSI92 syntax, outer joins between any 2 tables are ending up in the From clause of the query which is normal. But for the kind of self restricting outer joins i mentioned above, how do we solve?

Thanks

Dave Sharon (BOB member since 2004-06-02)

2

Hi, use this:

(T1.OCCUPATION_CODE = 'CODE3'  OR T1.OCCUPATION_CODE IS NULL)

That normally does the trick.

Regards,

Gerard

highandstoned :netherlands: (BOB member since 2005-08-01)

3

Although this solution is old, the SQL is still valid :wink:, and helped me out of a similar situtation.

CZAPJA :us: (BOB member since 2008-11-10)

4

Excellent info; exactly what I needed even though my joins were more complex than the one shown above. A typical join that was corrected:

Before:
OTTR_PATIENT_DEMO2.PAT_ID=OTTR_ACTIONS_donor2.PAT_ID(+) AND OTTR_ACTIONS_donor2.ACT_ID IN (10706, 10707)

After:
(OTTR_PATIENT_DEMO2.PAT_ID=OTTR_ACTIONS_donor2.PAT_ID AND OTTR_ACTIONS_donor2.ACT_ID IN (10706, 10707)) OR OTTR_ACTIONS_donor2.PAT_ID IS NULL

:smiley:

ladeau (BOB member since 2008-12-31)

5

Thanks. This bit of code is extremely helpful and works like a charm. Records would drop off due to self-restricting joins, now they don’t.

Join:
PS_ACAD_PLAN.ACAD_PLAN=PS_ACAD_PLAN_OWNER.ACAD_PLAN

Self-restricting join:

(PS_ACAD_PLAN_OWNER.EFFDT = (
SELECT MAX (B.EFFDT)
FROM PS_ACAD_PLAN_OWNER B
WHERE PS_ACAD_PLAN_OWNER.ACAD_PLAN = B.ACAD_PLAN
AND B.EFFDT<=SYSDATE)) or PS_ACAD_PLAN_OWNER.ACAD_PLAN IS NULL
:smiley:

heronseye :us: (BOB member since 2008-11-12)