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)
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.
I've formatted your query to make it a little more palatable.
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.