Recursive Subquerying with Sorting

Recursive Subquerying with sorting

Initially, I could not see a more elegant solution than to create a temp table.

I was thinking, what a awkward dialect of SQL Oracle is:

Why no IF TABLE EXISTS DELETE TABLE?
Why do I have to do EXECUTE IMMEDIATE with a string? Why can't I just do DROP TABLE TEMP on its own?
Why can't I have ORDER BY without nesting in parentheses on ANCHOR?
Why can't I have ORDER BY on recursive SELECT after UNION ALL?
SQL WITH needs standardising. Other database dialects don't necessitate column names being parenthesised on WITH statement. If you don't do that you get some meaningless ALIAS error, at the point of the recursive join after UNION ALL.
Pagination: See here No LIMIT / OFFSET

DECLARE
 v_c NUMBER;
BEGIN
SELECT COUNT(*) INTO v_c FROM user_tables WHERE TABLE_NAME = 'TEMP';
IF v_c = 1 THEN
  EXECUTE IMMEDIATE 'DROP TABLE TEMP';
END IF;
END;
CREATE TABLE TEMP AS  (
    SELECT * FROM (
      SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE
      FROM TIDAL.JOBMST
      WHERE JOBMST_PRNTID IS NOT NULL
      ORDER BY JOBMST_PRNTID, JOBMST_NAME
    )
);
WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
  SELECT * FROM (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
    ORDER BY JOBMST_NAME
  )
  UNION ALL
  SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
  FROM TEMP J2
  INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
  WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_ID SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ;

Then (mathguy on the Oracle Community Forum) pointed out to me my SEARCH DEPTH FIRST should have just been by JOBMST_NAME.

Then it all falls into place:

WITH J1(JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, LVL) AS  (
    SELECT JOBMST_ID, JOBMST_NAME, JOBMST_PRNTID, JOBMST_TYPE, 1
    FROM TIDAL.JOBMST
    WHERE JOBMST_PRNTID IS NULL
    UNION ALL
    SELECT J2.JOBMST_ID, J2.JOBMST_NAME, J2.JOBMST_PRNTID, J2.JOBMST_TYPE, J1.LVL + 1
    FROM TIDAL.JOBMST J2
    INNER JOIN J1 ON J2.JOBMST_PRNTID = J1.JOBMST_ID
    WHERE J2.JOBMST_PRNTID IS NOT NULL
)
SEARCH DEPTH FIRST BY JOBMST_NAME SET DISP_SEQ
SELECT *
FROM J1
ORDER BY DISP_SEQ

Ordering recursive result set in SQL Server

Is the number of siblings a known value? Is the number of levels known?
If so, you can perform operations over the ItemOrder, to guarantee that every item has a unique ItemOrder, and then just sort by that value.

For example, suppose that any item can't have more than 10 childs (ItemOrder ranges from 0 to 9) and there are at most 5 levels. What I'm going to do now, is to make the first parent ItemOrder to be 10000 time it's current item order, ant it's childer ItemOrder would be 1000 times it's current ItemOrder plus it's parent ItemOrder, and so on, removing a 0 each time you go a level down.

WITH Parents AS
(
SELECT MenuItemId,
    URL,
    ParentItemId,
    (ItemOrder * 10000) AS ItemOrder,
    10000 AS Multiplier
FROM CambsMenu
WHERE ParentItemId IS NULL

UNION ALL

SELECT si.MenuItemId,
    si.URL,
    si.ParentItemId,
    (p.ItemOrder + si.ItemOrder * p.Multiplier/ 10) as ItemOrder,
    (p.Multiplier / 10) as Multiplier
FROM CambsMenu si INNER JOIN Parents p
ON si.ParentItemId = p.MenuItemId
)

SELECT * FROM Parents ORDER BY ItemOrder

If the number of levels or children is unknown, you can go with a similar approach but instead of building a numeric ItemOrder you can build a string ItemOrder, guaranteeing that the string '1.10.20' is lower than the string '2.1'

How to use a recursive query as a subquery?

Just remove the filtering condition from the anchor part:

WITH    q AS
        (
        SELECT  ID_CUSTOMER, ID_CUSTOMER AS root_customer
        FROM    CUSTOMERS c
        UNION ALL
        SELECT  c.ID_CUSTOMER, q.root_customer
        FROM    q
        JOIN    CUSTOMERS c 
        ON      c.ID_PARENT_CUSTOMER = q.ID_CUSTOMER
        )
SELECT  *
FROM    q

root_customer will show you the root of the chain.

Note that the same customers may be returned several times.

Say, a grandchild will be return at least thrice: in its grandparent tree, its parent tree and in its own tree, but each time with a different root_customer.

Use subquery inside a CTE so that I can use recursion

I thought it was just a bad reference on the JOIN in the recursive part, but that's not quite the issue.

You're referencing CTE, which can be thought of as the previous iteration of the recursion. But for your "current iteration", you're trying to reference ScriptTbl2, but ScriptTbl2 only exists within the "anchor" query - you have to redo that query with ROW_NUMBER(), etc. to build up your own ScriptTbl2 in the recursive part.

Maybe like this:

DECLARE @EndCreateTableScript varchar(20) = ') ON [PRIMARY] ';
DECLARE @NewLine varchar(2) = CHAR(13) + CHAR(10);
DECLARE @createTableScript varchar(max)
SET @createTableScript = 'CREATE TABLE [' 

;WITH CTE
AS (
    SELECT ScriptTbl2.RowNumber, ScriptTbl2.CreateTableStart, ScriptTbl2.ColumnScript, ScriptTbl2.EndTableScript
    FROM
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY TableName ORDER BY TableName) AS RowNumber, TableName, 
                    CreateTableStart, ColumnTextStart + DataSizeText + ColumnNullText + @NewLine AS ColumnScript, @EndCreateTableScript + TextImageScript AS EndTableScript
            FROM
            (
                SELECT DISTINCT SchemaName, TableName, @createTableScript + SchemaName + '].[' + TableName + '] ( ' + @NewLine AS CreateTableStart,
                    '[' +ColName + '] [' + DataType + '] ' AS ColumnTextStart,  
                    CASE WHEN DataType in ('bit', 'int', 'money', 'datetime') THEN ' '
                         WHEN DataType in ('numeric', 'decimal') THEN '(' + DataTypePrecision + ', ' + DataTypeScale + ') '
                         WHEN CAST(DataTypeMaxLength AS INT) = -1 THEN '(max) '
                         WHEN DataType in ('varchar', 'varbinary') THEN '('+ DataTypeMaxLength +') '
                         WHEN DataType = 'nvarchar' THEN  '('+ CAST(CAST(DataTypeMaxLength AS INT)/2 AS varchar(5)) +') '
                    END AS DataSizeText,    
                    CASE IsColumnNullable WHEN '0' THEN 'NOT NULL,' ELSE 'NULL,' END AS ColumnNullText,
                    CASE WHEN TextImageFileGroup IS NOT NULL THEN 'TEXTIMAGE_ON [' + TextImageFileGroup + ']' ELSE '' END AS TextImageScript                                                                                                 
                FROM #DBObjectsToAdd
            ) AS ScriptTbl
        ) AS ScriptTbl2
    WHERE RowNumber = 1
    UNION ALL
    SELECT CTE.RowNumber, CTE.CreateTableStart, CTE.ColumnScript + ' ' + ScriptTbl2.ColumnScript, CTE.EndTableScript 
    FROM CTE JOIN (
        SELECT ScriptTbl2.RowNumber, ScriptTbl2.CreateTableStart, ScriptTbl2.ColumnScript, ScriptTbl2.EndTableScript
        FROM (
            SELECT ROW_NUMBER() OVER (PARTITION BY TableName ORDER BY TableName) AS RowNumber, TableName, 
                    CreateTableStart, ColumnTextStart + DataSizeText + ColumnNullText + @NewLine AS ColumnScript, @EndCreateTableScript + TextImageScript AS EndTableScript
            FROM
            (
                SELECT DISTINCT SchemaName, TableName, @createTableScript + SchemaName + '].[' + TableName + '] ( ' + @NewLine AS CreateTableStart,
                    '[' +ColName + '] [' + DataType + '] ' AS ColumnTextStart,  
                    CASE WHEN DataType in ('bit', 'int', 'money', 'datetime') THEN ' '
                         WHEN DataType in ('numeric', 'decimal') THEN '(' + DataTypePrecision + ', ' + DataTypeScale + ') '
                         WHEN CAST(DataTypeMaxLength AS INT) = -1 THEN '(max) '
                         WHEN DataType in ('varchar', 'varbinary') THEN '('+ DataTypeMaxLength +') '
                         WHEN DataType = 'nvarchar' THEN  '('+ CAST(CAST(DataTypeMaxLength AS INT)/2 AS varchar(5)) +') '
                    END AS DataSizeText,    
                    CASE IsColumnNullable WHEN '0' THEN 'NOT NULL,' ELSE 'NULL,' END AS ColumnNullText,
                    CASE WHEN TextImageFileGroup IS NOT NULL THEN 'TEXTIMAGE_ON [' + TextImageFileGroup + ']' ELSE '' END AS TextImageScript                                                                                                 
                FROM #DBObjectsToAdd
            ) AS ScriptTbl
        ) AS ScriptTbl2
    ) AS ScriptTbl3
    ON ScriptTbl3.RowNumber = CTE.RowNumber + 1 --you want each iteration to increase, right?
)
SELECT *
FROM CTE

Recursive Subquerying with Sorting