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))
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.