INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle SQL convert Oracles (+) join for SAS program

Oracle SQL convert Oracles (+) join for SAS program

(OP)
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
;

RE: Oracle SQL convert Oracles (+) join for SAS program


You can use the ANSI "JOIN" operation:

CODE

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; 
3eyes

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

RE: Oracle SQL convert Oracles (+) join for SAS program

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

Have fun.

---- Andy

RE: Oracle SQL convert Oracles (+) join for SAS program

(OP)
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.

RE: Oracle SQL convert Oracles (+) join for SAS program

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close