Recursive with multiple first lines

Question

Recursive with multiple first lines

ross.hughes

Old Hand

Points: 313
More actions
May 2, 2018 at 4:17 pm

#410474

I have a 4 level job explosion of shortage parts where the first level sometimes has multiple entries. How do I write the recursive SQL string so it explodes each line individually? Or is it the same as a any recursive approach?
In the example attached I want it to look like the following between the Parent Job and the PO shortage columns.
--1
----2
------3
--1
--1
----2
--1
----2
------3
etc...
Right now it looks like this:
--1
--1
--2
--2
--2
--3
etc...
I don't need to add levels because they are already here in this dataset and I can use them. Is there an easy query that will solve this?
Thanks!
Ross

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 1

If you could post some readily consumable data (see the first link in my signature line below under "Helpful Links" for how to do that), I'd be happy to scribe a bit of code that would demonstrate this along with the expected sort order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ross.hughes Old Hand Points: 313 More actions · Answer 2

Thanks, Jeff. I have reformulated my data based on your feedback below.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
        (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
  Level INT,
  ParentJobNumber VARCHAR(14),
  ParentJobPartNumber VARCHAR(50),
  ParentJobDesc VARCHAR(100),
  ShortagePartNum VARCHAR(50),
  ShortageDesc VARCHAR(100),
  SupplyJob VARCHAR(14),
        )

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, Level, ParentJobNumber, ParentJobPartNumber,ParentJobDesc, ShortagePartNum, ShortageDesc, SupplyJob)
SELECT 1,1,'012582','PBC110111101','HARI MASA SO#205999 PBC 6000,R/L,230/3 STD','17377','ASSY, PBC HEATER, 230V, 3000W, R/L','013667' UNION ALL
SELECT 2,1,'012582','PBC110111101','HARI MASA SO#205999 PBC 6000,R/L,230/3 STD','PBC6000R/L MECH ONLY','PBC 6000 R/L STD MECHANICS ONLY','012583' UNION ALL
SELECT 3,2,'012583','PBC6000R/L MECH ONLY','PBC 6000 R/L STD MECHANICS ONLY','17708','ASSY, FRAME PBC6000 UL','013946' UNION ALL
SELECT 4,2,'012583','PBC6000R/L MECH ONLY','PBC 6000 R/L STD MECHANICS ONLY','SB38161SS','SCREW, SOC BTTN 3/8-16 UNC X 1 SS',NULL UNION ALL
SELECT 5,2,'012583','PBC6000R/L MECH ONLY','PBC 6000 R/L STD MECHANICS ONLY','SCM825','SCREW, SOC CAP M8 X 25MM',NULL UNION ALL
SELECT 6,3,'013946','17708','ASSY, FRAME PBC6000 UL','17440','ASSY, ACME SCREW DRIVE','013138' UNION ALL
SELECT 7,3,'013946','17708','ASSY, FRAME PBC6000 UL','17441','ASSY, ACME SCREW DRIVEN','013139' UNION ALL
SELECT 8,3,'013946','17708','ASSY, FRAME PBC6000 UL','17457','WELDMENT, PEDESTAL PBC','013943' UNION ALL
SELECT 9,3,'013946','17708','ASSY, FRAME PBC6000 UL','17756','WELDMENT,OVERARM STD PBC 6000 UL','010944' UNION ALL
SELECT 10,4,'013943','17457','WELDMENT, PEDESTAL PBC','17651','PAD, BASE WELDMENT BOLT','013923'--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF

What I am trying to do is start with Level 1 and create a recursive relationship by following the relationship of the SUPPLYJOB on the current line be the PARENTJOBNUM of the next level, if there is one. Otherwise, show the next level 1, etc.

Thanks for the help post better and for considering a solution. I really appreciate it.

Regards,
Ross

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 3

Having a look now... thanks for taking the time to make the data readily consumable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 4

First, what you've asked for is to determine the hierarchy of a "forest", meaning that it has more than one root and, therefor, more than 1 tree.

The type of hierarchy you have is called an "Adjacency List", which a lot of people refer to as a "Parent/Child" hierarchy. In this case, the SupplyJob column is the child and contains the required unique job numbers.

The following code will resolve the hierarchy for you. I recalculated the level for two reasons. First, to demonstrate that it's correctly calculated by you comparing the hLevel column I calculated with the Level column that you provided and, second, so that you wouldn't actually have to rely on someone else's code being correct. See the comment in the code about this.

WITH cteRoots AS ( SELECT hLevel = 1 ,hPath = CONVERT(VARBINARY(4000),CONVERT(BINARY(4),CONVERT(INT,ISNULL(SupplyJob,'9999999')))) ,* FROM #mytable WHERE [Level] = 1 --Would be better if this were WHERE ParentJobNumber = '012582' so you don't rely on someone else's code being right UNION ALL SELECT hLevel = cte.hLevel + 1 ,hPath = CONVERT(VARBINARY(4000),cte.hPath + CONVERT(BINARY(4),CONVERT(INT,ISNULL(tbl.SupplyJob,'9999999')))) ,tbl.* FROM #mytable tbl JOIN cteRoots cte ON tbl.ParentJobNumber = cte.SupplyJob ) SELECT * FROM cteRoots ORDER BY hPath ;
For more information on how this all works, especially how the hPath (Hierarchical Path) column works, please see the following article. It also explains how to create Nested Sets, which is another hierarchical structure that results in nasty fast queries.
http://www.sqlservercentral.com/articles/Hierarchy/94040/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ross.hughes Old Hand Points: 313 More actions · Answer 5

Wow, Jeff. That is awesome and I so appreciate it! It works perfectly and I learned something new.

Thank you!
Ross

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 6

Great. Thanks for the feedback, Ross.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ross.hughes Old Hand Points: 313 More actions · Answer 7

Hi Jeff,

I ran in to a little wrinkle in this line:

hPath = CONVERT(VARBINARY(4000),CONVERT(BINARY(4),CONVERT(INT,ISNULL(SupplyJob,'9999999'))))

My Supply Job Number is often a string and it gives me the "Conversion failed when converting the varchar value 'I19RU' to data type int." error.

How would I edit this final chunk to preserve the sorting you have laid out?

Thanks,
Ross

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 8

ross.hughes - Friday, May 11, 2018 7:03 AM
Hi Jeff,
I ran in to a little wrinkle in this line:
hPath = CONVERT(VARBINARY(4000),CONVERT(BINARY(4),CONVERT(INT,ISNULL(SupplyJob,'9999999'))))
My Supply Job Number is often a string and it gives me the "Conversion failed when converting the varchar value 'I19RU' to data type int." error.
How would I edit this final chunk to preserve the sorting you have laid out?
Thanks,
Ross

What is the maximum number of characters that the Supply Job Number can be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ross.hughes Old Hand Points: 313 More actions · Answer 9

ross.hughes

Old Hand

Points: 313

May 11, 2018 at 7:17 am

#1990184

14

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 10

Hmmm.... is there an integer column on the original supply job table that acts like a surrogate key for the Supply Job Number?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 11

I've not tested it because I'm on my way to work but I believe this will get you to where you want to be with the character based Supply Job Numbers. It's not quite as efficient as working with INTs converted to a BINARY(4) but should work reasonably well. It works on precisely the same principles as before, just using a different fixed-length datatype.

WITH cteRoots AS ( SELECT hLevel = 1 ,hPath = CONVERT(VARCHAR(8000),CONVERT(CHAR(14),ISNULL(SupplyJob,'ZZZZZZZZZZZZZZ'))) ,* FROM #mytable WHERE [Level] = 1 --Would be better if this were WHERE ParentJobNumber = '012582' so you don't rely on someone else's code being right UNION ALL SELECT hLevel = cte.hLevel + 1 ,hPath = CONVERT(VARCHAR(8000),cte.hPath + CONVERT(CHAR(14),ISNULL(SupplyJob,'ZZZZZZZZZZZZZZ')))) ,tbl.* FROM #mytable tbl JOIN cteRoots cte ON tbl.ParentJobNumber = cte.SupplyJob ) SELECT * FROM cteRoots ORDER BY hPath ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ross.hughes Old Hand Points: 313 More actions · Answer 12

Okay, thanks. That sets me in the in a direction.

I am getting an error 'Incorrect syntax near '.' on this but will work with it to see if i can get it to work.

Thanks, Jeff.

Ross

ross.hughes Old Hand Points: 313 More actions · Answer 13

That looks like it is working, Jeff. There was only an extra parenthesis on the second line change.

Again, I really appreciate your help and have a better idea of what you are doing now.

Regards,
Ross

Jeff Moden SSC Guru Points: 1003942 More actions · Answer 14

ross.hughes - Friday, May 11, 2018 8:26 AM
That looks like it is working, Jeff. There was only an extra parenthesis on the second line change.
Again, I really appreciate your help and have a better idea of what you are doing now.
Regards,
Ross

Glad to help. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)