This discussion is archived
4 Replies Latest reply: Apr 5, 2012 3:32 PM by 84959 RSS

convert this sql server query to Oracle?

691406 Newbie
Currently Being Moderated
I've some SQL Server queries that I need to make a copy for to run against Oracle databases. (10G), any help would be appreciated.


SELECT COUNT(*) COUNT, CONVERT(DATETIME, CONVERT(VARCHAR(8), M.START_DATE, 1)) [DATE]
     FROM OPTC.ORD_M_ORDER M
     INNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.PAT_NUMBER = P.PAT_NUMBER
     INNER JOIN OPTC.ORD_C_ORDER_TYPE C ON C.ORDER_TYPE_KEY = M.ORDER_TYPE_KEY
     WHERE OGEN.DATEONLY(END_DATE)- OGEN.DATEONLY(START_DATE) < '1900/03/01'
     --AND OGEN.DATEONLY(CREATED_ON) = OGEN.DATEONLY(CURRENT_TIMESTAMP)
     AND ORDER_STATUS IN (4,7,8)
     AND M.FACILITY_KEY IN (SELECT VALUE FROM OGEN.COMMA_TO_TABLE(@FACILITYKEY))
     AND [START_DATE] BETWEEN @STARTDATE AND @ENDDATE
     AND CAST(M.ORDER_FLAGS AS BIGINT) & 64 <> 64 AND P.DISCHARGE_DATE IS NULL
     GROUP BY CONVERT(DATETIME, CONVERT(VARCHAR(8), [START_DATE], 1))
  • 1. Re: convert this sql server query to Oracle?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi,
    You could use the 'Translation Scratch Editor' in SQL*Developer to translate SQL*Server statements to Oracle.
    SQL*Developer is free and can be downloaded from here -

    http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

    Once installed start it up, then go to 'Tools' - 'Migration' - 'Translation Scratch Editor'

    I did run the statement you have posted here through it but it gave errors so looks like there may be a problem with the syntx in any case.
    Does this query run as it is in SQL*Server ?

    Regards,
    Mike

    Edited by: mkirtley on Jan 5, 2012 12:45 PM
  • 2. Re: convert this sql server query to Oracle?
    691406 Newbie
    Currently Being Moderated
    yes it runs ok. i will hav a look at that tool thanks!
  • 3. Re: convert this sql server query to Oracle?
    irishred Newbie
    Currently Being Moderated
    I am using this tool in Oracle SQL Developer, the Scratch Editor, and I have it set to the correct translation (M'soft SQL Server to PL/SQL) and it's skipping almost every line of code. I tried to play around with different translations in the editor with no luck (just to see what it did).

    I really do NOT recommend this tool, it's useless. Basically if you can't figure it out, get a book on the language or ask a colleague to translate for you if you have enough pride to do so.
  • 4. Re: convert this sql server query to Oracle?
    84959 Explorer
    Currently Being Moderated
    Hello

    It is possible that you are running into a bug in the translation scratch editor. Make sure that you are using the latest version of SQL developer I.e. 3107. If possible post your query that you are trying to convert. We can try to determine the root cause. I am sure you found SQL developer useful for many other tasks. You can also try putting the SQL in a file and convert using the command line API.

    Regards

    Prakash

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points