SSC-FDM-OR0004

Siblings keyword removed from the order by clause because Snowflake does not support it.

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

In Oracle, the ORDER BY SIBLINGS clause can be used in hierarchical queries to preserve the order of the data given by the hierarchy, while applying a reorder of the values that are siblings in the same hierarchy. This is not supported in Snowflake.

Example Code

Input Code:

IN -> Oracle_01.sql

SELECT LEVEL,
       LPAD(' ', 2 * (LEVEL - 1)) || NAME AS FORMATTED_NAME,
       JOB_TITLE
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY NAME;

Output Code:

OUT -> Oracle_01.sql

SELECT LEVEL,
       NVL(
       LPAD(' ', 2 * (
                      !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!LEVEL - 1)) :: STRING, '') || NVL(NAME :: STRING, '') AS FORMATTED_NAME,
       JOB_TITLE
FROM
       EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY
       PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER BY
       NAME /*** SSC-FDM-OR0004 - SIBLINGS KEYWORD REMOVED FROM ORDER BY CLAUSE BECAUSE SNOWFLAKE DOES NOT SUPPORT IT ***/;

• While the exact same ordering achieved with the SIBLINGS clause might not be accessible, there are a few alternatives to get a similar result.

  • Embed the query within an outer query that applies the desired sorting using ORDER BY.

  • Create a CTE with the hierarchical query using CONNECT BY and reference the CTE in a subsequent query to apply ORDER BY for sibling sorting (rows at the same level).

• If you need more support, you can email us at [email protected]