Error in Recursive CTE query 1

[Katerine]

Hello,
I have something that I hope will be an obvious fix, to anybody who knows more about recursive CTE queries than I do. It's probably something obvious...

I'm working with a VBScript module which loads a TTX file through a SQL statement (because that's how this software package works... can't really do anything about that). It worked fine... until I tried to change it from a basic "grab all of the components of this job" query, to a recursive query (grab all components, and all components of those components).

Issue: Nothing has changed. I'm still only seeing the first level of components in the (Crystal Reports) report, not the components and all of their subcomponents.
(There was an error about aggregate functions not being allowed in the recursive part of a recursive query, but I got rid of that)

SQL Server version is unknown (to me, at least). The database is actually kind of a black box - we can only play with the front-end and Crystal Reports. My guess: probably 2005, or older.

Here's the TSQL, with the wrapping VBScript:

WITH q AS (
[indent]SELECT Bill_Of_Jobs.Parent_Job, 
[indent]Bill_Of_Jobs.Component_Job[/indent] 
FROM Bill_Of_Jobs 
WHERE Bill_Of_Jobs.Relationship_Type = 'Component' 
[indent]AND Bill_Of_Jobs.Parent_Job = '" & sJob & "' //note: this is still from the VBScript wrapper. sJob is a calling parameter of the VBScript function.[/indent]
UNION ALL 
SELECT Bill_Of_Jobs.Parent_Job, 
[indent]Bill_Of_Jobs.Component_Job[/indent] 
FROM Bill_Of_Jobs INNER JOIN q ON Bill_Of_Jobs.Parent_Job = q.Component_Job 
[/indent]) 
SELECT q.Parent_Job, 
[indent]q.Component_Job, 
Job.Part_Number, 
Job.Description, 
Job.Ext_Description, 
Job.Rev, 
Job.Status, 
Job.Make_Quantity, 
Bill_Of_Jobs.Relationship_Qty, 
(SELECT Sum(Job_Operation.Act_Setup_Labor) 
[indent]FROM Job_Operation 
WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Act_Setup_Labor, [/indent]
(SELECT Sum(Job_Operation.Act_Run_Labor) 
[indent]FROM Job_Operation 
WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Act_Run_Labor, [/indent]
(SELECT Sum(Job_Operation.Est_Setup_Labor) 
[indent]FROM Job_Operation 
WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Est_Setup_Labor,[/indent] 
(SELECT Sum(Job_Operation.Est_Run_Labor) 
[indent]FROM Job_Operation 
WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Est_Run_Labor,[/indent] 
(SELECT Sum(Material_Req.Est_Total_Cost) 
[indent]FROM Material_Req 
WHERE Material_Req.Job = Bill_Of_Jobs.Component_Job) AS Mat_Est_Total_Cost,[/indent] 
(SELECT Sum(Material_Req.Act_Total_Cost) 
[indent]FROM Material_Req 
WHERE Material_Req.Job = Bill_Of_Jobs.Component_Job) AS Mat_Act_Total_Cost,[/indent] 
Job.Total_Price AS cEstCost, 
Job.Total_Price AS cActCost 
[/indent]FROM ((Bill_Of_Jobs INNER JOIN Job ON Bill_Of_Jobs.Component_Job = Job.Job) 
[indent]INNER JOIN q ON Bill_Of_Jobs.Component_Job = q.Component_Job)[/indent] 
WHERE Bill_Of_Jobs.Relationship_Type = 'Component' 
[indent]AND Bill_Of_Jobs.Parent_Job = '" & sJob & "' // again, a holdover from the VBScript here[/indent]

...and here's the unedited code with the VBScript wrapping intact (just in case the issue is with the wrapping):

	sql = "WITH q AS (" _
		& "SELECT Bill_Of_Jobs.Parent_Job, " _
				& "Bill_Of_Jobs.Component_Job " _
				& "FROM Bill_Of_Jobs " _
				& "WHERE Bill_Of_Jobs.Relationship_Type = 'Component' " _
				& "AND Bill_Of_Jobs.Parent_Job = '" & sJob & "' " _
		& "UNION ALL " _
		& "SELECT Bill_Of_Jobs.Parent_Job, " _
				& "Bill_Of_Jobs.Component_Job " _
				& "FROM Bill_Of_Jobs INNER JOIN q ON Bill_Of_Jobs.Parent_Job = q.Component_Job " _
		& ") " _
		& "SELECT q.Parent_Job, " _
				& "q.Component_Job, " _
				& "Job.Part_Number, " _
				& "Job.Description, " _
				& "Job.Ext_Description, " _
				& "Job.Rev, " _
				& "Job.Status, " _
				& "Job.Make_Quantity, " _
				& "Bill_Of_Jobs.Relationship_Qty, " _
				& "(SELECT Sum(Job_Operation.Act_Setup_Labor) " _
					& "FROM Job_Operation " _
					& "WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Act_Setup_Labor, " _
				& "(SELECT Sum(Job_Operation.Act_Run_Labor) " _
					& "FROM Job_Operation " _
					& "WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Act_Run_Labor, " _
				& "(SELECT Sum(Job_Operation.Est_Setup_Labor) " _
					& "FROM Job_Operation " _
					& "WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Est_Setup_Labor, " _
				& "(SELECT Sum(Job_Operation.Est_Run_Labor) " _
					& "FROM Job_Operation " _
					& "WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job) AS JO_Est_Run_Labor, " _
				& "(SELECT Sum(Material_Req.Est_Total_Cost) " _
					& "FROM Material_Req " _
					& "WHERE Material_Req.Job = Bill_Of_Jobs.Component_Job) AS Mat_Est_Total_Cost, " _
				& "(SELECT Sum(Material_Req.Act_Total_Cost) " _
					& "FROM Material_Req " _
					& "WHERE Material_Req.Job = Bill_Of_Jobs.Component_Job) AS Mat_Act_Total_Cost, " _
				& "Job.Total_Price AS cEstCost, " _
				& "Job.Total_Price AS cActCost " _
				& "FROM ((Bill_Of_Jobs INNER JOIN Job ON Bill_Of_Jobs.Component_Job = Job.Job) " _
					& "INNER JOIN q ON Bill_Of_Jobs.Component_Job = q.Component_Job) " _
				& "WHERE Bill_Of_Jobs.Relationship_Type = 'Component' " _
				& "AND Bill_Of_Jobs.Parent_Job = '" & sJob & "' "

Many thanks!

Katie

 

[gk53]

I think it should be

;WITH q (Parent_Job, Component_Job) AS (
	SELECT Parent_Job = Bill_Of_Jobs.Parent_Job,
			Component_Job = Bill_Of_Jobs.Component_Job
			FROM Bill_Of_Jobs 
			WHERE Bill_Of_Jobs.Relationship_Type = 'Component'
			AND Bill_Of_Jobs.Parent_Job = '" & sJob & "' 
		UNION ALL 
	SELECT Parent_Job = Bill_Of_Jobs.Parent_Job,
			Component_Job = Bill_Of_Jobs.Component_Job
			FROM Bill_Of_Jobs 
				INNER JOIN q 
			ON Bill_Of_Jobs.Parent_Job = q.Component_Job
) 
SELECT q.Parent_Job, 
		q.Component_Job, 
		Job.Part_Number, 
		Job.Description, 
		Job.Ext_Description, 
		Job.Rev, Job.Status, 
		Job.Make_Quantity, 
		Bill_Of_Jobs.Relationship_Qty, 
		JO_Act_Setup_Labor = (SELECT Sum(Job_Operation.Act_Setup_Labor) 
				FROM Job_Operation 
					WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job),
		JO_Act_Run_Labor = (SELECT Sum(Job_Operation.Act_Run_Labor) 
				FROM Job_Operation 
					WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job),
		JO_Est_Setup_Labor = (SELECT Sum(Job_Operation.Est_Setup_Labor) 
				FROM Job_Operation 
					WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job),
		JO_Est_Run_Labor = (SELECT Sum(Job_Operation.Est_Run_Labor) 
				FROM Job_Operation 
					WHERE Job_Operation.Job = Bill_Of_Jobs.Component_Job),
		Mat_Est_Total_Cost = (SELECT Sum(Material_Req.Est_Total_Cost) 
				FROM Material_Req 
					WHERE Material_Req.Job = Bill_Of_Jobs.Component_Job),
		Mat_Act_Total_Cost = (SELECT Sum(Material_Req.Act_Total_Cost)
				FROM Material_Req 
					WHERE Material_Req.Job = Bill_Of_Jobs.Component_Job),
		cEstCost = Job.Total_Price, 
		cActCost = Job.Total_Price
		FROM Bill_Of_Jobs 
			INNER JOIN Job 
				ON Bill_Of_Jobs.Component_Job = Job.Job 
			INNER JOIN q 
				ON Bill_Of_Jobs.Component_Job = q.Component_Job
		WHERE Bill_Of_Jobs.Relationship_Type = 'Component' 
		AND Bill_Of_Jobs.Parent_Job = '" & sJob & "'

 

[Katerine]

Many thanks!

I discovered the issue, which was twofold:
[ol 1]
[li]The final "AND Bill_Of_Jobs.Parent_Job = '" & sJob & "' " (last line of the query) was defeating the purpose of having a recursive loop[/li]
[li]There was a Selection filter applied in Crystal Reports, which limited records shown to those where the Parent_Job equaled the "master" job[/li]
[/ol]

I plugged in your query (minus the final line) as a command in a new report, and it returned the correct records. But it still didn't return the records in the real report, which is when I discovered the filter. Removed the filter, and it worked great.

Thanks again!

Katie