CODEWITHSUNDEEP
sql-serveroracle-databasesortinggroup-byrecursive-query
user1058946
user1058946

Reputation: 283

Sorting Multiple Parent/Child on Recursive SQL Query

I'm converting some Oracle code to SQL Server.

The Oracle code looks like this:

SELECT FLEX_VALUE, DESCRIPTION, ADMIN_ENABLED, PARENT_FLEX_VALUE, DISPLAY_DESC, LEVEL 
FROM (  SELECT FLEX_VALUE, DESCRIPTION, ADMIN_ENABLED, PARENT_FLEX_VALUE, vDescField AS DISPLAY_DESC 
        FROM GL_SEGMENT2 
        WHERE PERIOD_YEAR = 2015; ) 
CONNECT BY PRIOR FLEX_VALUE = PARENT_FLEX_VALUE 
START WITH PARENT_FLEX_VALUE IS NULL 
ORDER SIBLINGS BY DISPLAY_DESC;

And it produces the following CORRECT RESULTS: Oracle Query Results (sample)

The Query groups the data by their parent, and the levels are indicated. The results are ordered by the parent groups. The 'children' under the group headings don't seem to be ordered. The data is stored in a single table.

I have converted the Oracle query to the following SQL Server query:

WITH n ([FLEX_VALUE], [DESCRIPTION], [ADMIN_ENABLED], [PARENT_FLEX_VALUE], [DISPLAY_DESC], [LEVEL]) AS 
    (SELECT P1.[FLEX_VALUE], P1.[DESCRIPTION], P1.[ADMIN_ENABLED], P1.[PARENT_FLEX_VALUE], P1.[DISPLAY_DESC], 1 AS [LEVEL] 
     FROM (SELECT [FLEX_VALUE], [DESCRIPTION], [ADMIN_ENABLED], [PARENT_FLEX_VALUE], [FLEX_VALUE] + ' - ' + [DESCRIPTION] AS [DISPLAY_DESC] 
           FROM dbo.FIN_REP_GL_SEGMENT2 
           WHERE [PERIOD_YEAR] = 2015 ) AS P1 
     WHERE LEN(LTRIM(RTRIM(ISNULL(P1.[PARENT_FLEX_VALUE],'')))) = 0 

     UNION ALL 

     SELECT C1.[FLEX_VALUE], C1.[DESCRIPTION], C1.[ADMIN_ENABLED], C1.[PARENT_FLEX_VALUE], C1.[DISPLAY_DESC], Parent.[LEVEL] + 1 
     FROM (SELECT [FLEX_VALUE], [DESCRIPTION], [ADMIN_ENABLED], [PARENT_FLEX_VALUE], [FLEX_VALUE] + ' - ' + [DESCRIPTION] AS [DISPLAY_DESC] 
           FROM dbo.FIN_REP_GL_SEGMENT2 
           WHERE [PERIOD_YEAR] = 2015 ) AS C1 

     JOIN n Parent ON Parent.[FLEX_VALUE] = C1.[PARENT_FLEX_VALUE] ) 

SELECT [FLEX_VALUE], [DESCRIPTION], [ADMIN_ENABLED], [PARENT_FLEX_VALUE], [DISPLAY_DESC], [LEVEL]
FROM n
ORDER BY [DISPLAY_DESC]

The above SQL Server query produces INCORRECT SORTING as illustrated below:

Bad SQL Query

The LEVELS seem correct but the children are being displayed under the incorrect parent categories (note the B145 and Cnnn values). The B145 record should display under the F000 parent, and the Cnnn records should display under the L000 parent. Currently the SQL Query puts these under the B000 parent which is incorrect!

The SQL query seems to be sorting on the FLEX_VALUE column, irrespective of what 'parent' the 'child' actually belongs to.

The root cause [sic] of the issue seems to be that there are MULTIPLE root records with NULL in their PARENT_FLEX_VALUE, and I actually want to ignore the alphabetic sorting on FLEX_VALUE (I'm only concerned with the PARENT SORT ORDER).

Everything I try with the SQL query doesn't change the sorting order.

Other than the sorting/grouping issue, the query is basically working.

A re-worked example of my current SQL Server query attempt with an explanation of why it currently doesn't work will be very much appreciated.

Upvotes: 3

Views: 1215

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

This Oracle query similar to yours, recursive, should help. I think you can easily modify it to SQL Server version:

with t(FV, DSC, PFV, path, lvl) as (
  select FLEX_VALUE, DESCRIPTION, PARENT_FLEX_VALUE, flex_value, 1 
    from gl_segment2 where parent_flex_value is null
  union all
  select g.FLEX_VALUE, g.DESCRIPTION, g.PARENT_FLEX_VALUE,
         t.path||'/'||g.flex_value, t.lvl+1
    from gl_segment2 g join t on g.parent_flex_value = t.fv )
select t.*, lpad(' ', (lvl-1)*2, ' ')||fv hierarchy from t order by path

In order to keep hierarchy I added path column which enables correct ordering. Of course you don't need columns PATH, LVL, HIERARCHY in output, I added them only for presentation puproses.

Output and SQLFiddle:

FV    DSC                  PFV   PATH                     LVL HIERARCHY 
----- -------------------- ----- ----------------- ---------- ----------
A000  DESCRIPTION A000           A000                       1 A000
A010  DESCRIPTION A010     A000  A000/A010                  2   A010
A100  DESCRIPTION A100     A010  A000/A010/A100             3     A100
A101  DESCRIPTION A101     A010  A000/A010/A101             3     A101
A011  DESCRIPTION A011     A000  A000/A011                  2   A011
B000  DESCRIPTION B000           B000                       1 B000
B010  DESCRIPTION B010     B000  B000/B010                  2   B010
B011  DESCRIPTION B011     B000  B000/B011                  2   B011
F000  DESCRIPTION F000           F000                       1 F000
B145  DESCRIPTION B145     F000  F000/B145                  2   B145

Upvotes: 2

Related Questions