Recursive code help

hermie · March 18, 2020, 5:01pm

I have the following code that I need to get the recursive feature in the script...

Given...REPLACED_PART_NUMBER, feed get the PART_NUMBER column of the result tbl, and repeat until NULL returns.

SELECT distinct PART_NUMBER, REPLACED_PART_NUMBER
FROM myTbl
where REPLACED_PART_NUMBER in ('PN001','PN002',...)
order by PART_NUMBER;

Thanks.

yosiasz · March 18, 2020, 5:10pm

provide sample data
the expected result from the sample data

hermie · March 18, 2020, 5:27pm

PART_NUMBER, REPLACED_PART_NUMBER
PN201, PN101
PN202, PN102
PN203, PN103
PN301, PN201
PN302, PN202
PN303, PN203

My first run result gets only the PN2XX,
2nd run results get the PN3XX,
3rd run results get NULL

desired result: replacement table
PN101, PN201
PN101, PN301
...
or
PN101, PN201, PN301, NULL

Hope this example make sense...

yosiasz · March 18, 2020, 7:16pm

As indicated in previous post please create proper ddl and dml

Create table #sample

Insert into #sample

hermie · March 18, 2020, 7:21pm

Thanks yosiasz. Will do it as soon as I am able. Trying to brute force my way through now.

yosiasz · March 18, 2020, 7:47pm

brute force what?

hermie · March 19, 2020, 4:20am

Long story, I have to cut/paste the result each time to excel and do post processing.
Create table #sample (
Item int,
Part_Num varchar(5),
Replaced_Part_Num varchar(5),
Part_Noun varchar(20)
)
Insert into #sample
select 1 ,'PN501','PN401', 'PartName01' union
select 2 ,'PN502','PN402', 'PartName02' union
select 3 ,'PN503','PN403', 'PartName03' union
select 4 ,'PN401','PN301', 'PartName11' union
select 5 ,'PN3402','PN302', 'PartName12' union
select 6 ,'PN403','PN303', 'PartName13' union
select 7 ,'PN2301','PN201', 'PartName21' union
select 8 ,'PN302','PN202', 'PartName22' union
select 9 ,'PN303','PN203', 'PartName23' union
select 10 ,'PN201','PN101', '2_rpld_pn' union
select 11 ,'PN202','PN102', '3_rpld_pn' union
select 12 ,'PN203','PN103', '4_rpld_pn'
;
SELECT PART_NUM, REPLACED_PART_NUM
FROM #sample
where REPLACED_PART_NUM in ('PN101','PN202','103')
order by PART_NUM
drop table #sample

Got a data truncate message. Anyhow, the desired output should be as follows:
PN101, PN201, 2_rpld_pn
PN101, PN2301, 2_rpld_pn
PN102, PN202, 3_rpld_pn
PN102, PN302, 3_rpld_pn
PN102, PN3402, 3_rpld_pn
PN103, PN203, 4_rpld_pn
PN103, PN303, 4_rpld_pn
PN103, PN403, 4_rpld_pn
PN103, PN503, 4_rpld_pn

Thanks!

yosiasz · March 19, 2020, 4:28am

So you want people that are volunteering to help you out to also to fix that problem?

harishgg1 · March 19, 2020, 4:37am

hi

somethings wrong with the data ..

SELECT
    [S].[Item]
    , [S].[Part_Num]
    , [S].[Replaced_Part_Num]
    , [S].[Part_Noun]
FROM
    [#sample] AS [S]
WHERE
    [S].[Part_Num] = 'PN101';

please click arrow to the left for DROP Create Hermie Sample Data

DROP TABLE [#sample];

CREATE TABLE [#sample]
     (
         [Item] INT
         , [Part_Num] VARCHAR(10)
         , [Replaced_Part_Num] VARCHAR(10)
         , [Part_Noun] VARCHAR(20)
     );
INSERT INTO [#sample]
SELECT  1, 'PN501', 'PN401', 'PartName01'
UNION
SELECT  2, 'PN502', 'PN402', 'PartName02'
UNION
SELECT  3, 'PN503', 'PN403', 'PartName03'
UNION
SELECT  4, 'PN401', 'PN301', 'PartName11'
UNION
SELECT  5, 'PN3402', 'PN302', 'PartName12'
UNION
SELECT  6, 'PN403', 'PN303', 'PartName13'
UNION
SELECT  7, 'PN2301', 'PN201', 'PartName21'
UNION
SELECT  8, 'PN302', 'PN202', 'PartName22'
UNION
SELECT  9, 'PN303', 'PN203', 'PartName23'
UNION
SELECT  10, 'PN201', 'PN101', '2_rpld_pn'
UNION
SELECT  11, 'PN202', 'PN102', '3_rpld_pn'
UNION
SELECT  12, 'PN203', 'PN103', '4_rpld_pn';

for data truncate you need to increase the size of the varchar fields

Part_Num varchar(5),
Replaced_Part_Num varchar(5),
to
Part_Num varchar(20),
Replaced_Part_Num varchar(20),

Hope this helps

hermie · March 19, 2020, 12:19pm

Thanks Mike01, up the varchar(10), (10), (30) respectively. no truncation msg anymore! Output is blank though in SQL Fiddle.

hermie · March 19, 2020, 12:25pm

Didn't know the field varchar need to be more than the number of characters. The first two PN has only 5 char, so I thought it's engough. the 3rd field has less than 10, so I gave it 20, still not enough? So, I didn't know that's what the error msg is telling me. Now, I know, so I increased the size to 10, 10 and 30. No more truncation msg. Thanks.

Got the schema to work.

MS SQL Server 2017 Schema Setup:

    Create table sample (
        Item int,
        Part_Num varchar(10),
        Replaced_Part_Num varchar(10),
        Part_Noun varchar(30)
    )
    Insert into sample
    select 1 ,'PN501','PN401', 'PartName01' union
    select 2 ,'PN502','PN402', 'PartName02' union
    select 3 ,'PN503','PN403', 'PartName03' union
    select 4 ,'PN401','PN301', 'PartName11' union
    select 5 ,'PN3402','PN302', 'PartName12' union
    select 6 ,'PN403','PN303', 'PartName13' union
    select 7 ,'PN2301','PN201', 'PartName21' union
    select 8 ,'PN302','PN202', 'PartName22' union
    select 9 ,'PN303','PN203', 'PartName23' union
    select 10 ,'PN201','PN101', '2_rpld_pn' union
    select 11 ,'PN202','PN102', '3_rpld_pn' union
    select 12 ,'PN203','PN103', '4_rpld_pn'
    ;
    SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [sample] AS [S]
    WHERE
        [S].[Part_Num] = 'PN101';

mike01 · March 19, 2020, 3:30pm

try this

  Create table #Sample (
        Item int,
        Part_Num varchar(6),
        Replaced_Part_Num varchar(5),
        Part_Noun varchar(30)
    )
    Insert into #Sample
    select 1 ,'PN501','PN401', 'PartName01' union
    select 2 ,'PN502','PN402', 'PartName02' union
    select 3 ,'PN503','PN403', 'PartName03' union
    select 4 ,'PN401','PN301', 'PartName11' union
    select 5 ,'PN3402','PN302', 'PartName12' union
    select 6 ,'PN403','PN303', 'PartName13' union
    select 7 ,'PN2301','PN201', 'PartName21' union
    select 8 ,'PN302','PN202', 'PartName22' union
    select 9 ,'PN303','PN203', 'PartName23' union
    select 10 ,'PN201','PN101', '2_rpld_pn' union
    select 11 ,'PN202','PN102', '3_rpld_pn' union
    select 12 ,'PN203','PN103', '4_rpld_pn'
    ;
 ;with cte as
 (   SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [#Sample] AS [S]
    WHERE
        [S].[Replaced_Part_Num] = 'PN102'
	union all
	  SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [#Sample] AS [S]
			join cte c
				on s.Replaced_Part_Num = c.Part_Num		)

	select * from cte
		
		;

JamesK · March 19, 2020, 5:22pm

Recursive CTEs are notoriously poor performers, at least in my experience. So if you have a decent amount of data in the table, or if you need very good performance (e.g., a procedure that is invokded very frequently), then CTE may not be the best solution.

When I had to do something like this, I added a new column to hold the desired data. In my case, I needed the root entry (in your case, the very first part number before any replacements), so the new column would hold the root entry for all the rows that have as the root. If in your case you need the opposite - i.e., the latest part number that was replaced for any given part - it may be a bit more work to maintain, but still worth considering.

hermie · March 19, 2020, 5:22pm

Thanks mike01...I need to learn how CTE works. So, will be watching some youtube video and come back and look at your code. Got the SQL Fiddle to work. My ignorance of not know beforehand. Here are the codes. Thanks for your patience. By the way, I modified the schema to make things consistent.

Create table Sample (
Item int,
Part_Num varchar(6),
Replaced_Part_Num varchar(5),
Part_Noun varchar(30)
)
Insert into Sample
select 1 ,'PN501','PN401', 'PartName501' union
select 2 ,'PN502','PN402', 'PartName502' union
select 3 ,'PN503','PN403', 'PartName503' union
select 4 ,'PN401','PN301', 'PartName401' union
select 5 ,'PN3402','PN302', 'PartName3403' union
select 6 ,'PN403','PN303', 'PartName403' union
select 7 ,'PN2301','PN201', 'PartName2301' union
select 8 ,'PN302','PN202', 'PartName302' union
select 9 ,'PN303','PN203', 'PartName303' union
select 10 ,'PN201','PN101', 'PartName201' union
select 11 ,'PN202','PN102', 'PartName202' union
select 12 ,'PN203','PN103', 'PartName203' union
select 10 ,'PN101','NULL', 'PartName101' union
select 11 ,'PN102','NULL', 'PartName102' union
select 12 ,'PN103','NULL', 'PartName103'
;
Query 1:

with cte as
 (   SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
    WHERE
        [S].[Replaced_Part_Num] = 'PN102'
	union all
	  SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
			join cte c
				on s.Replaced_Part_Num = c.Part_Num		)

	select * from cte

[Results][2]:

| Item | Part_Num | Replaced_Part_Num |    Part_Noun |
|------|----------|-------------------|--------------|
|   11 |    PN202 |             PN102 |  PartName202 |
|    8 |    PN302 |             PN202 |  PartName302 |
|    5 |   PN3402 |             PN302 | PartName3403 |

hermie · March 19, 2020, 5:24pm

Will keep your advise in mind. Thanks JamesK.

hermie · March 19, 2020, 5:25pm

Thanks for your patience. Got the schema to work on Fiddle.

hermie · March 20, 2020, 12:43pm

This works great! I changed the Replace_Part_Num to Part_Num, so all the correct Part_Num would result in first col. Thanks again!

  WHERE
        [S].[Part_Num] IN ('PN101', 'PN102','PN103')

[Results][2]:

| Item | Part_Num | Replaced_Part_Num |    Part_Noun |
|------|----------|-------------------|--------------|
|   10 |    PN101 |              NULL |  PartName101 |
|   11 |    PN102 |              NULL |  PartName102 |
|   12 |    PN103 |              NULL |  PartName103 |
|   12 |    PN203 |             PN103 |  PartName203 |
|    9 |    PN303 |             PN203 |  PartName303 |
|    6 |    PN403 |             PN303 |  PartName403 |
|    3 |    PN503 |             PN403 |  PartName503 |
|   11 |    PN202 |             PN102 |  PartName202 |
|    8 |    PN302 |             PN202 |  PartName302 |
|    5 |   PN3402 |             PN302 | PartName3403 |
|   10 |    PN201 |             PN101 |  PartName201 |
|    7 |   PN2301 |             PN201 | PartName2301 |

hermie · March 23, 2020, 3:19am

Hi mike01, In what circumstances will I get a "non-recursive WITH clause or view should not reference itself within its own definitions". I have pretty much the exact cte structures as you have. And, I even execute each statement separate and see the results to be representative to what I would expect. As soon as I put it back to the cte format, I got the above message.

By the way, I watched kudvenkat's recursive cte part 3 on youtube, and followed his example. I understand recursive cte a lot better than last week. Thanks.

mike01 · March 23, 2020, 12:50pm

Were you able to create the view? or is this what you get when you query it?

hermie · March 23, 2020, 12:55pm

Unable to get the view, query has errors...could this be platform specific issues? e.g. Top 1 vs Limit 1 statement. Also, when googling recursive cte last night, there were mention of having "recursive" in front of the cte name, e.g. with recursive cte_name...Thx.