sql server to oracle(sql plus)

[mah kumar]

hi all
i have forwarded a small piece of sql server code...i want an equivalent of this in oracle..
iam a bit confused with using "case" and "joins"
please help to solve it out ...
also provide me links where i can learn the same

the code piece is as follows:

SELECT DISTINCT rd.RD_Resdtlid,rh.rs_resid,rd.RD_AccNo,Rh.RS_Title,rd.RD_ownerstaff,pub.PU_PubName,rd.Flowid,elm.Location_Name,
CASE WHEN rd.rd_resuseStatus <> 'A' THEN isnull(emp.EMP_FIRSTNAME, '') + ' ' + isnull(emp.EMP_MIDDLENAME, '') + ' ' + isnull(emp.EMP_LASTNAME, '') ELSE 'Available in Library' END AS Availability

FROM ERM_EL_TR_ResHdr Rh INNER JOIN
ERM_EL_MA_Publisher pub ON Rh.PU_PubId = pub.PU_PubId INNER JOIN
ERM_EL_TR_ResDtl rd ON Rh.RS_ResId = rd.RS_ResId LEFT OUTER JOIN
ERM_EMPLOYEE_MASTER emp ON rd.RD_CurUserStaff = emp.EMP_STAFFID INNER JOIN
ERM_Location_Master elm ON rd.LO_LocId = elm.Location_Id
WHERE (Rh.RS_Status = 'A') AND (rd.RD_Status = 'A') AND (rd.RD_UserAction = 'C') and rd.flowid in(a_strFlowID)


regards
mk

[Paul Sturrock]

That looks like ANSI complient SQL to me, so you shouldn't have to change anything to run it against Oracle. [url=http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10759/toc.htm[/ul/] have a fairly comprehensive SQL reference online.

[Julian Kennedy]

Hi Mah,

I've formatted your query to make it a little more palatable.

SELECT
DISTINCT rd.RD_Resdtlid
        ,rh.rs_resid
        ,rd.RD_AccNo
        ,Rh.RS_Title
        ,rd.RD_ownerstaff
        ,pub.PU_PubName
        ,rd.Flowid
        ,elm.Location_Name
        ,CASE WHEN rd.rd_resuseStatus <> 'A'
              THEN isnull(emp.EMP_FIRSTNAME, '') + ' '
                 + isnull(emp.EMP_MIDDLENAME, '') + ' '
                 + isnull(emp.EMP_LASTNAME, '')
              ELSE 'Available in Library'
         END AS Availability 
 
FROM             ERM_EL_TR_ResHdr Rh
INNER JOIN       ERM_EL_MA_Publisher pub ON Rh.PU_PubId        = pub.PU_PubId
INNER JOIN       ERM_EL_TR_ResDtl rd     ON Rh.RS_ResId        = rd.RS_ResId
LEFT OUTER JOIN  ERM_EMPLOYEE_MASTER emp ON rd.RD_CurUserStaff = emp.EMP_STAFFID
INNER JOIN       ERM_Location_Master elm ON rd.LO_LocId        = elm.Location_Id
 
WHERE    Rh.RS_Status = 'A'
     AND rd.RD_Status = 'A'
     AND rd.RD_UserAction = 'C'
     AND rd.flowid IN (a_strFlowID)

As far as I can see you have 3 issues:

1. You will need to replace the CASE for Availability with the Oracle DECODE() function, which operates a bit like an IF/THEN/ELSE.

2. You'll need to replace all uses of ISNULL() with Oracle's equivalent IFNULL()

3. You'll need to replace a_strFlowID, in the last predicate of the WHERE clause, with a list of values for the IN clause. This last bit implies that the query is from a stored procedure. I don't think you can do the equivalent in Oracle (8i anyway) without resorting to dynamic SQL.

For future reference, if you make your code a little easier to read in the forum, using indentation and the UBB CODE button, you may get a better response.

I hope this helps.

Jules
[ August 03, 2004: Message edited by: Julian Kennedy ]