Oracle query - SQL SERVER query conversion- Help

Copper Contributor

Hi

Planning to migrate the oracle query into the SQL server 

below is the part of the ORACLE query, noticing errors at "START WITH" and "fetch the first row only." in SQL server studio while running.

Any pointers would be helpful. 

 

 

SELECT F1.FILE_ID, F1.REVISION, F1.REVISION_TIME,F1.NAMESPACE, F1.FULL_PATH,F1.OWNED_BY_NAME, F1.OWNED_BY_TYPE,F1.EXT_ATTRIBUTES,F1.SYS_CREATED_BY_NAME,F1.SYS_CREATED_BY_TYPE,F1.SYS_MODIFIED_BY_NAME,F1.SYS_MODIFIED_BY_TYPE,F1.ACL_ID, ROW_NUMBER() OVER (PARTITION BY F1.LOOKUP_KEY, F1.PATH_DEPTH ORDER BY F1.REVISION_TIME DESC) RANK FROM BI180123.CSS_SI_FILES F1 WHERE F1.SERVICE_INSTANCE_ID = 1 AND F1.NAMESPACE = 'content:catalog' START WITH F1.PARENT_ID=(SELECT FT.FILE_ID FROM BI180123.CSS_SI_FILES FT WHERE UPPER(convert(varchar, FT.FULL_PATH))= UPPER('/shared') AND FT.NAMESPACE= 'content:catalog' fetch first row only) CONNECT BY F1.PARENT_ID = PRIOR F1.FILE_ID ORDER BY FILE_ID

 

1 Reply