Oracle SQL convert Oracles (+) join for SAS program

[niebs2]

Hello, I am trying to convert an Oracle script that uses (+) outer join sign so a SAS program that runs multiple scripts can run this as well. This script is to get an Assessment taken with all questions whether they answered them or not and return all the responses. Responses have data in the rows that match but if they didnt answer them then there is no data just questions not answered. Any help converting this would be greatly appreciated. I have tried many different ways but only get back answered questions. Thanks.

select ases.ases_key, ases.ases_id, ases.ases_desc, ases.ases_quest_key, ases.ases_quest_id, ases.ases_quest_desc, t.cse_id, t.mbr_ases_rspn_key, t.mbr_ases_key, t.rspn_txt
from
(select ASMT.ASES_KEY, ASMT.ASES_ID, ASES_DESC, Q.ASES_QUEST_KEY, Q.ASES_QUEST_ID, Q.ASES_QUEST_DESC from
ASSESSMENT ASMT
, QUESTION Q
where ASMT.ASES_KEY = Q.ASES_KEY
and ASMT.ASES_ID = 'CCS451') ASES
,

(select MA.ASES_KEY, MA.CSE_ID, MAR.RSPN_TXT
FROM
MEMBER_ASSESSMENT MA
, MEMBER_ASSESSMENT_RESPONSE MAR
WHERE MA.CSE_ID = '160177398'
AND MAR.MBR_ASES_KEY = MA.MBR_ASES_KEY(+)
) T
where T.ASES_KEY(+) = ASES.ASES_KEY
and T.ASES_QUEST_KEY(+) = ASES.ASES_QUEST_KEY
;

 

[LKBrwnDBA]

You can use the ANSI "JOIN" operation:

SELECT ases.ases_key, ases.ases_id, ases.ases_desc
     , ases.ases_quest_key, ases.ases_quest_id, ases.ases_quest_desc
     , t.cse_id, t.mbr_ases_rspn_key, t.mbr_ases_key
     , t.rspn_txt
  FROM (SELECT asmt.ases_key, asmt.ases_id, ases_desc, q.ases_quest_key
             , q.ases_quest_id, q.ases_quest_desc
          FROM assessment asmt, question q
         WHERE asmt.ases_key = q.ases_key
           AND asmt.ases_id = 'CCS451') ases
       LEFT OUTER JOIN (SELECT ma.ases_key, ma.cse_id, mar.rspn_txt
                          FROM member_assessment ma
                               LEFT OUTER JOIN member_assessment_response mar
                                 ON ma.cse_id = '160177398'
                                AND mar.mbr_ases_key = ma.mbr_ases_key) t
         ON t.ases_key = ases.ases_key
        AND t.ases_quest_key(+) = ases.ases_quest_key;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

SQLs with (+) are pre-1999 (last century) syntax. Orcale is still fine with them, but you should join (pun intended ) XXI century and use Left Join, Right Join, etc.

Have fun.

---- Andy

 

[niebs2]

LKBrwnDBA - thank you for the response. when i run it i get a invalid identifier on "T"."ASES_QUEST_KEY". If i take that out it runs but i get back way to many rows of data. Also the outer join (+) is still there on last row of code.

 

[niebs2]

I have the last part figured out and have it working without any problems. Thanks for getting me there. no more (+) signs. i also had to bring the cse_id and ases_id after the ON condition to help eliminate duplicate rows.