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))
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 -
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 ?
Edited by: mkirtley on Jan 5, 2012 12:45 PM
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.
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.