4 Replies Latest reply: Apr 5, 2012 5:32 PM by Pnauduri-Oracle RSS

    convert this sql server query to Oracle?

    691406
      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
          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
            yes it runs ok. i will hav a look at that tool thanks!
            • 3. Re: convert this sql server query to Oracle?
              irishred
              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?
                Pnauduri-Oracle
                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