none
SSMS 2012: Doing recursive CTE - output has more rows than the rows of input. Why? Some Strange Values of Total_costs!!??

    Question

  • Hi all,

    In the dbo.airplane Table of sample Database in my SQL Server 2012 Management Studio (SSMS 2012), there are 11 rows of input data:

    I execute the following code for a recursive CTE:

    Use sample;
    WITH list_of_parts(assembly, quantity, total_costs) AS
      (
         SELECT containing_assembly, quantity_contained, unit_cost
           FROM dbo.airplane
    	   WHERE contained_assembly IS NULL
        UNION ALL
         SELECT a.containing_assembly, a.quantity_contained,
         CAST(l.quantity*l.total_costs AS DECIMAL(6,2))
           FROM list_of_parts l, airplane a
           WHERE l.assembly = a.contained_assembly
       )
    SELECT * FROM list_of_parts; 

    It worked nicely. But I got 13 rows of output in my Result:

    I don't understand why I got 4 output of Airplane results with the strange values:

    Row#         assembly          quantity        total_costs

    6                Airplane                1               12.00

    7                Airplane                1                22.00

    9                Airplane                1                15.00

    13              Airplane                1                 13.00

    I cannot figure out how these values came from the expression "l.quantity*l.costs"  !!!???

    Please kindly help and advise.

    Thanks in advance,

    Scott Chang  

    Wednesday, April 24, 2013 5:44 PM

All replies

  • I suggest you to post your code not as screen shots (which are useless for people helping) but as INSERT statements instead. In any case, it's easy to explain extra rows especially if you add an extra column to your output 

    Use sample;
    WITH list_of_parts(Root, assembly, quantity, total_costs) AS
      (
         SELECT containing_assembly as Root, containing_assembly, quantity_contained, unit_cost
           FROM dbo.airplane
    	   WHERE contained_assembly IS NULL
        UNION ALL
         SELECT l.Root, a.containing_assembly, a.quantity_contained,
         CAST(l.quantity*l.total_costs AS DECIMAL(6,2))
           FROM list_of_parts l INNER JOIN airplane a
           ON l.assembly = a.contained_assembly
       )
    SELECT * FROM list_of_parts; 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 24, 2013 5:51 PM
    Moderator
  • Hi Naomi, Thanks for your response.

    I tried to present my post as clear as possible.  I don't know how to do the "INSERT" thing you said.  Please kindly help and explain to me why I have these strange results.

    Thanks again,

    Scott Chang

    Wednesday, April 24, 2013 6:05 PM
  • In order for us to help you, we want to have DDL of the table and your rows as INSERT statements so we would be able to copy them into a query window and test the script. With the screen shots we need to do all this work manually ourselves and it takes lot of time.

    So, in order to generate DDL and INSERT scripts follow these steps:

    1. Right click on your database name in SSMS

    2. Select Tasks/Generate scripts

    3. Chose objects, select the table you need to script

    4. Scripting options, select New query window and go to Advanced

    5. Scroll down to the 'Type of data to script' and select 'Schema and Data'

    6. Proceed with scripting (click Next and Finish)

    7. Copy that generated script into the forum's post.

    UPDATE. Before copying that script you may want to edit it to make readable (e.g. instead of one line long insert move columns and values to separate lines).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Wednesday, April 24, 2013 6:18 PM
    Moderator
  • Hi Naomi,  Thanks for your nice response and valuable instructions.

    I followed your instructions and I got the following script.sql:

    USE [sample]
    GO
    /****** Object:  Table [dbo].[airplane]    Script Date: 4/24/2013 3:09:58 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[airplane](
    	[containing_assembly] [varchar](10) NULL,
    	[Contained_assembly] [varchar](10) NULL,
    	[quantity_contained] [int] NULL,
    	[unit_cost] [decimal](6, 2) NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Airplane', N'Fuselage', 1, CAST(10.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Airplane', N'Wings', 1, CAST(11.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Airplane', N'Tail', 1, CAST(12.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Fuselage', N'Cockpit', 1, CAST(13.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Fuselage', N'Cabin', 1, CAST(14.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Fuselage', N'Nose', 1, CAST(15.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Cockpit', NULL, 1, CAST(13.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Cabin', NULL, 1, CAST(14.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Nose', NULL, 1, CAST(15.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Wings', NULL, 2, CAST(11.00 AS Decimal(6, 2)))
    INSERT [dbo].[airplane] ([containing_assembly], [Contained_assembly], [quantity_contained], [unit_cost]) VALUES (N'Tail', NULL, 1, CAST(12.00 AS Decimal(6, 2)))

    Please use it and explain to me what happened and why I got the strange results.

    Many Thanks,

    Scott Chang

    Wednesday, April 24, 2013 7:42 PM
  • So, following my original suggestion we can add Root column to show how recursive CTE works:

    ;
    
    WITH list_of_parts (
    	[Root]
    	,[assembly]
    	,quantity
    	,total_costs
    	)
    AS (
    	SELECT containing_assembly AS [Root]
    		,containing_assembly
    		,quantity_contained
    		,unit_cost
    	FROM dbo.airplane
    	WHERE contained_assembly IS NULL
    	
    	UNION ALL
    	
    	SELECT l.[Root]
    		,a.containing_assembly
    		,a.quantity_contained
    		,CAST(l.quantity * l.total_costs AS DECIMAL(6, 2))
    	FROM list_of_parts l
    	INNER JOIN airplane a ON l.assembly = a.contained_assembly
    	)
    SELECT *
    FROM list_of_parts
    ORDER BY [Root]
    	,[Assembly];



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 24, 2013 7:57 PM
    Moderator
  • Hi Naomi,

    I did what you gave me in your last response.

    Here are the script I used and result I got:

    WITH list_of_parts(
        [Root]
    	,[assembly]
    	,quantity
    	,total_costs
    	)
    AS(
        SELECT containing_assembly AS [Root]
    	       ,containing_assembly
    		   ,quantity_contained
    		   ,unit_cost
    	FROM dbo.airplane
    	WHERE contained_assembly IS NULL
    	UNION ALL
    	SELECT l.[Root]
    	      ,a.containing_assembly
    		  ,a.quantity_contained
    		  ,CAST(l.quantity*l.total_costs AS DECIMAL(6, 2))
    	FROM list_of_parts l
    	INNER JOIN airplane a ON l.assembly = a.contained_assembly
    	)
    SELECT *
    FROM list_of_parts
    ORDER BY [Root]
         ,[Assembly]; 

    I am still not able to understand the Root, "l.quantity*l.total_costs" expression and the values of total_costs. Please kindly explain them for me.

    Many Thanks,

    Scott Chang

    Thursday, April 25, 2013 12:24 PM
  • Actually, you're right - I am not sure why we only calculate total cost in the recursive part of cte and not in the anchor part. I think in the first part we also should use quantity * unit_cost as total_cost.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 25, 2013 12:46 PM
    Moderator
  • The tree set is defined correctly. The parent-child  relationships are mixed up.

    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Thursday, May 02, 2013 8:34 AM
    Moderator