SSC-FDM-OR0004

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

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 [e-mail]