locked
MIgrate Oracle Recursive query to SQL SERVER RRS feed

  • Question

  • Requesting help/guidance to migrate this Oracle query to SQL SERVER - 

     WITH 
                 FILES(FILE_ID, REVISION, PARENT_ID) AS 
                 (SELECT A.FILE_ID, A.REVISION, A.PARENT_ID 
                    FROM (SELECT F.FILE_ID, F.REVISION, F.PARENT_ID, F.IS_DELETED,  
                    ROW_NUMBER() OVER (PARTITION BY F.LOOKUP_KEY, F.PATH_DEPTH ORDER BY F.REVISION_TIME DESC) RANK  
                            FROM historyTableName F  
                           WHERE F.SERVICE_INSTANCE_ID = ? 
                             AND F.NAMESPACE = ? 
                             AND F.REVISION_TIME <= ?) A 
                    WHERE A.RANK = 1 
                      AND A.IS_DELETED = ?), 
                 FILE_TREE (FILE_ID, REVISION, PARENT_ID) AS 
                 (SELECT F.FILE_ID, F.REVISION, F.PARENT_ID 
                    FROM FILES F  
                   WHERE F.PARENT_ID = ? 
                  UNION ALL  
                  SELECT F.FILE_ID, F.REVISION, F.PARENT_ID 
                    FROM FILES F, FILE_TREE FT 
                   WHERE F.PARENT_ID = FT.FILE_ID) 
                 SEARCH DEPTH FIRST BY FILE_ID SET FILE_ORDER 
                 SELECT F.parentKeyColumnName, F.FILE_ID, F.PARENT_ID, F.FULL_PATH, F.NAME,  
                        F.NAMESPACE, F.REVISION, F.REVISION_TIME,  
                        F.IS_DELETED, F.ITEM_TYPE, F.CONTENT_TYPE, 
                        F.OBJ_TYPE, F.OBJ_TYPE_VER, F.TARGET_PATH, 
                        F.RENAMED_FROM_PATH, F.OWNED_BY_NAME, 
                        F.OWNED_BY_TYPE, F.SYS_CREATED_BY_NAME,
                        F.SYS_CREATED_BY_TYPE, F.SYS_MODIFIED_BY_NAME, 
                        F.SYS_MODIFIED_BY_TYPE, F.SYS_CREATED_TIME,
                        F.SYS_MODIFIED_TIME, F.CREATED_BY_NAME,  
                        F.CREATED_BY_TYPE, F.MODIFIED_BY_NAME, 
                        F.MODIFIED_BY_TYPE, F.CREATED_TIME, 
                        F.MODIFIED_TIME, F.LANGUAGE,  
                        F.CONTENT_CHARSET, F.CONTENT_LENGTH, 
                        F.CONTENT_MODIFIED_TIME, F.CUST_FLAGS, F.EXT_ATTRIBUTES,  
                        F.CONTENT_ID, F.CONTENT_EXT_ID, F.CONTENT_UUID, 
                        F.CONTENT_IL_DATA, A.ACL_ID, A.ACL_JSON,  
                        CASE WHEN F.ITEM_TYPE = 'folder' THEN 1 ELSE 0 END AS IS_FOLDER  
                        FROM FILE_TREE IV, 
                        aclGroupsTableName A, 
                        historyTableName F 
                  WHERE IV.FILE_ID = F.FILE_ID 
                    AND IV.REVISION = F.REVISION 
                  AND F.ACL_ID = A.ACL_ID (+)  
                 ORDER BY FILE_ORDER

    Thanks.

    Friday, May 8, 2020 4:34 AM

Answers

  • Hi KrishnaPartha,

    Thank you for your issue . 

    I am sorry that I am not good at Oracle . In your script , I could not understand clearly . Could you please explain more ? 

    ----'?'

    In your script , you use many '?'. What is '?'

    ----' SEARCH DEPTH FIRST BY '

    I am not sure which one can vice it. It is used to order . Right ?


    ----'FROM FILE_TREE IV, 
                        aclGroupsTableName A, 
                        historyTableName F 
                  WHERE IV.FILE_ID = F.FILE_ID 
                    AND IV.REVISION = F.REVISION 
                  AND F.ACL_ID = A.ACL_ID (+) '

    Maybe please change it into following one . 

    FROM FILE_TREE IV 
    join aclGroupsTableName A on IV.FILE_ID = F.FILE_ID  and IV.REVISION = F.REVISION 
    left join historyTableName F on F.ACL_ID = A.ACL_ID 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by KrishnaPartha Thursday, May 14, 2020 9:30 PM
    Monday, May 11, 2020 7:05 AM

All replies

  • Hi KrishnaPartha,

    Thank you for your issue . 

    I am sorry that I am not good at Oracle . In your script , I could not understand clearly . Could you please explain more ? 

    ----'?'

    In your script , you use many '?'. What is '?'

    ----' SEARCH DEPTH FIRST BY '

    I am not sure which one can vice it. It is used to order . Right ?


    ----'FROM FILE_TREE IV, 
                        aclGroupsTableName A, 
                        historyTableName F 
                  WHERE IV.FILE_ID = F.FILE_ID 
                    AND IV.REVISION = F.REVISION 
                  AND F.ACL_ID = A.ACL_ID (+) '

    Maybe please change it into following one . 

    FROM FILE_TREE IV 
    join aclGroupsTableName A on IV.FILE_ID = F.FILE_ID  and IV.REVISION = F.REVISION 
    left join historyTableName F on F.ACL_ID = A.ACL_ID 

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by KrishnaPartha Thursday, May 14, 2020 9:30 PM
    Monday, May 11, 2020 7:05 AM
  • Hi Rachel

    ? in above script are bind variables. 

    SEARCH DEPTH FIRST BY  - To get hierarchical data with child rows to be returned before siblings are processed. This is to perform depth-first search.

    Thanks

    Thursday, May 14, 2020 4:12 AM
  • Hi KrishnaPartha, 

    For the moment, I don't know how to implement SEARCH DEPTH FIRST BY in SQL Server. I'm sorry that I still don't understand this function. Maybe you can provide your source data and your table structure, and I can try to rewrite your code.

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 14, 2020 7:19 AM
  • No problem. Anyways, thanks for the pointers. It helps.

    I have rewritten the query in SQL SERVER.. but need to test with data and see if this returns the data as expected..

                
                WITH
                FILES(FILE_ID, REVISION, PARENT_ID) AS
                (SELECT A.FILE_ID, A.REVISION, A.PARENT_ID
                   FROM (SELECT F.FILE_ID, F.REVISION, F.PARENT_ID, F.IS_DELETED, 
                        ROW_NUMBER() OVER (PARTITION BY F.LOOKUP_KEY, F.PATH_DEPTH ORDER BY F.REVISION_TIME DESC) RANK 
                           FROM historyTableName F 
                          WHERE F.SERVICE_INSTANCE_ID = ?
                            AND F.NAMESPACE = ?
                            AND F.REVISION_TIME <= ?) A
                   WHERE A.RANK = 1
                     AND A.IS_DELETED = ?),
                     
                FILE_TREE (FILE_ID, REVISION, PARENT_ID) AS
                (SELECT F.FILE_ID, F.REVISION, F.PARENT_ID
                   FROM FILES F 
                  WHERE F.PARENT_ID = ?
                 UNION ALL 
                 SELECT F.FILE_ID, F.REVISION, F.PARENT_ID
                   FROM FILES F, FILE_TREE FT
                  WHERE F.PARENT_ID = FT.FILE_ID)
                   SELECT F.parentKeyColumnName
                        , F.FILE_ID, F.PARENT_ID, F.FULL_PATH, F.NAME, 
                       F.NAMESPACE, F.REVISION, F.REVISION_TIME, 
                if (readOnly) {
                             0, 
                } else {
                             F.IS_DELETED,
                }
                       F.ITEM_TYPE, F.CONTENT_TYPE,
                       F.OBJ_TYPE, F.OBJ_TYPE_VER, F.TARGET_PATH, F.RENAMED_FROM_PATH, F.OWNED_BY_NAME,
                 F.OWNED_BY_TYPE, F.SYS_CREATED_BY_NAME, F.SYS_CREATED_BY_TYPE, F.SYS_MODIFIED_BY_NAME,
                 F.SYS_MODIFIED_BY_TYPE, F.SYS_CREATED_TIME, F.SYS_MODIFIED_TIME, F.CREATED_BY_NAME, 
                F.CREATED_BY_TYPE, F.MODIFIED_BY_NAME, F.MODIFIED_BY_TYPE, F.CREATED_TIME, F.MODIFIED_TIME, F.LANGUAGE, 
                 F.CONTENT_CHARSET, F.CONTENT_LENGTH, F.CONTENT_MODIFIED_TIME, F.CUST_FLAGS, F.EXT_ATTRIBUTES, 
                 F.CONTENT_ID, F.CONTENT_EXT_ID, F.CONTENT_UUID, F.CONTENT_IL_DATA, A.ACL_ID, A.ACL_JSON, 
                       CASE WHEN F.ITEM_TYPE = 'folder' THEN 1 ELSE 0 END AS IS_FOLDER 
                FROM CSS_SI_FILES_hist F
                JOIN FILE_TREE IV on IV.FILE_ID = F.FILE_ID  and IV.REVISION = F.REVISION 
                LEFT JOIN CSS_SI_ACL_GROUPS A on F.ACL_ID = A.ACL_ID 
                ORDER_BY FILE_ID;  

    Thursday, May 14, 2020 11:12 PM