This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes sql server to oracle(sql plus) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql server to oracle(sql plus)" Watch "sql server to oracle(sql plus)" New topic
Author

sql server to oracle(sql plus)

mah kumar
Greenhorn

Joined: Feb 05, 2004
Posts: 22
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Mah,

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.

    I hope this helps.

    Jules
    [ August 03, 2004: Message edited by: Julian Kennedy ]
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: sql server to oracle(sql plus)