Break out hierarchy from two columns in 1 table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Break out hierarchy from two columns in 1 table?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-06-02 : 17:28:56

I am trying to extract data from our job-shop ERP system, and one of the requests I've received is to see data related to our job operations grouped in a hierarchical manner, i.e. before something can be painted, it has to be taped, before it can be taped it has to be sanded, so on and so forth.

The ERP system *does* have a table with a field that establishes a relationship between a parent & child job, but some of these relationships go 5 and 6 tiers deep, as illustrated below:


ROOTJOB		JOBNO
          	20386-0000
20386-0000	20386-0009
20386-0009	20386-0012
20386-0009	20386-0028
20386-0009	20386-0059
20386-0012	20386-0013
20386-0012	20386-0014
20386-0012	20386-0015
20386-0028	20386-0054
20386-0059	20386-0062
20386-0062	20386-0063

So the hierarchy for that example would be:


20386-0000
	-> 20386-0009
		-> 20386-0012
			-> 20386-0013
			-> 20386-0014
			-> 20386-0015
		-> 20386-0028
			-> 20386-0054
		-> 20386-0059
			-> 20386-0062
				-> 20386-0063

That's a truncated accounting for the subjobs under job 20386-0000; this thing goes as long as my arm.

Now, assuming I can dump all this information into a series of dynamically-created datagrids(?), how would I even go about querying the data to 'build' these relationships out of what I've been given? Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-02 : 17:39:11

If you are using SQL Server 2005, this is a task for a recursive CTE.
I have posted many examples about using these here at SQLTeam, and Books Online has some examples too.

E 12°55'05.63"
N 56°04'39.26"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-03 : 04:39:25

As peso said this is a perfect CTE example.

The example in BOL shows you exactly how to make a tree spanning query.

If you are using Management Studio just go to help -> index and look for "common table expressions" The info you want is in the RECURSIVE CTE section.

They take a little work to get your head around bur after you work out how they work they can be very, very, useful. Half the battle is working out where that are going to be useful and where not though.

Good luck.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-03 : 05:32:20

[code]DECLARE @Sample TABLE
(
RootJob CHAR(10) NOT NULL,
JobNo CHAR(10) NOT NULL
)

INSERT @Sample
SELECT '', '20386-0000' UNION ALL
SELECT '20386-0000', '20386-0009' UNION ALL
SELECT '20386-0009', '20386-0012' UNION ALL
SELECT '20386-0009', '20386-0028' UNION ALL
SELECT '20386-0009', '20386-0059' UNION ALL
SELECT '20386-0012', '20386-0013' UNION ALL
SELECT '20386-0012', '20386-0014' UNION ALL
SELECT '20386-0012', '20386-0015' UNION ALL
SELECT '20386-0028', '20386-0054' UNION ALL
SELECT '20386-0059', '20386-0062' UNION ALL
SELECT '20386-0062', '20386-0063'

;WITH Yak (RootJob, JobNo, JobPath, Indent)
AS (
SELECT RootJob,
JobNo,
CAST(JobNo AS VARCHAR(MAX)),
0
FROM @Sample
WHERE RootJob = ''

UNION ALL

SELECT s.RootJob,
s.JobNo,
y.JobPath + ';' + s.JobNo,
y.Indent + 1
FROM Yak AS y
INNER JOIN @Sample AS s ON s.RootJob = y.JobNo
)

SELECT REPLICATE(' ', 5 * Indent) + JobNo AS JobNo,
RootJob,
Indent
FROM Yak
ORDER BY JobPath[/code]

E 12°55'05.63"
N 56°04'39.26"

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-06-03 : 17:33:24

Thanks for pointing me in the right direction, or at least, what seems to be the right direction right now :)

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-06-30 : 11:11:46

Sorry for dredging this from the depths, but I've run into another stumbling block and am hoping somebody might be able to shed some light on the subject.

I modified the example so that it worked with random job numbers:


DECLARE @JONO as varchar(15)
SET @JONO = '20386'

;WITH Yak (RootJob, JobNo, JobPath, Indent)
AS (
	SELECT	RootJob,
		JobNo,
		CAST(JobNo AS VARCHAR(MAX)),
		0
	FROM	
		(select
			jomast.fjobno as JobNo,
			jomast.fschbefjob as RootJob
		from
			jomast
		where
			left(fjobno, 5) in (@JONO)) As QQQ
	
	WHERE	RootJob = ''
	UNION ALL
	SELECT		
		s.RootJob,
		s.JobNo,
		y.JobPath + ';' + s.JobNo,
		y.Indent + 1
	FROM
		Yak AS y
		INNER JOIN	
		(select
			jomast.fjobno as JobNo,
			jomast.fschbefjob as RootJob
		from
			jomast
		where
			left(fjobno, 5) in (@JONO)) AS s 
	ON s.RootJob = y.JobNo
)

SELECT
		REPLICATE(' ', 5 * Indent) + JobNo AS JobNo
--		, RootJob
--		, Indent
FROM	
		Yak
ORDER BY	
		JobPath

And that is working fine. However, I need to link this hierarchical information to aggregate data from another query, one which looks like such:


DECLARE @JONO as varchar(15)
SET @JONO = '20386'

select
	jopact.fjobno as JOBNO,
	joitem.fpartno as PARTNO,
	SUM(jopact.flabact) as LABR_COSTS_DETAIL,
	SUM(jopact.fmatlact) as MATL_COSTS_DETAIL,
	SUM(jopact.fovhdact) as OVHD_COSTS_DETAIL,
	SUM(jopact.fsubact) as SUBS_COSTS_DETAIL,
	SUM(jopact.ftoolact) as TOOL_COSTS_DETAIL,
	SUM(jopact.fsetupact) as SETP_COSTS_DETAIL,
	SUM(jopact.fothract) as OTHR_COSTS_DETAIL,
	SUM(jopact.flabact + 
		jopact.fmatlact + 
		jopact.fovhdact + 
		jopact.fsubact + 
		jopact.ftoolact + 
		jopact.fsetupact + 
		jopact.fothract) 
	as TOTAL_COSTS_DETAIL
from 
	jopact
	left join joitem on jopact.fjobno = joitem.fjobno
where 
	left(jopact.fjobno, 5) in (@JONO)
group by
	jopact.fjobno,
	joitem.fpartno
order by 
	jopact.fjobno

I attempted to join the queries, but the only result of that has been a steady stream of errors. For illustrative purposes, my most recent attempt looks like this:


DECLARE @JONO as varchar(15)
SET @JONO = '20386'

SELECT
	RANKTABLE.JobNo AS JOBNO,
	DETAILS.PARTNO AS PARTNO,
	DETAILS.LABR_COSTS_DETAIL AS LABRC,
	DETAILS.MATL_COSTS_DETAIL AS MATLC,
	DETAILS.OVHD_COSTS_DETAIL AS OVHDC,
	DETAILS.SUBS_COSTS_DETAIL AS SUBSC,
	DETAILS.TOOL_COSTS_DETAIL AS TOOLC,
	DETAILS.SETP_COSTS_DETAIL AS SETPC,
	DETAILS.OTHR_COSTS_DETAIL AS OTHRC,
	DETAILS.TOTAL_COSTS_DETAIL AS TOTALC	
		
FROM
/*321, "RootJob" is not a recognized table hint*/
(Yak (RootJob, JobNo, JobPath, Indent)  
AS (
	SELECT	RootJob,
		JobNo,
		CAST(JobNo AS VARCHAR(MAX)),
		0
	FROM	
		(select
			jomast.fjobno as JobNo,
			jomast.fschbefjob as RootJob
		from
			jomast
		where
			left(fjobno, 5) in (@JONO)) As QQQ
	
	WHERE	RootJob = ''
	UNION ALL
	SELECT		
		s.RootJob,
		s.JobNo,
		y.JobPath + ';' + s.JobNo,
		y.Indent + 1
	FROM
		Yak AS y
		INNER JOIN	
		(select
			jomast.fjobno as JobNo,
			jomast.fschbefjob as RootJob
		from
			jomast
		where
			left(fjobno, 5) in (@JONO)) AS s 
	ON s.RootJob = y.JobNo
)

SELECT
		REPLICATE(' ', 5 * Indent) + JobNo AS JobNo
--		, RootJob
--		, Indent
FROM
/* Incorrect syntax near ')' here */	
		Yak) AS RANKS

JOIN

(select
	jopact.fjobno as JOBNO,
	joitem.fpartno as PARTNO,
	SUM(jopact.flabact) as LABR_COSTS_DETAIL,
	SUM(jopact.fmatlact) as MATL_COSTS_DETAIL,
	SUM(jopact.fovhdact) as OVHD_COSTS_DETAIL,
	SUM(jopact.fsubact) as SUBS_COSTS_DETAIL,
	SUM(jopact.ftoolact) as TOOL_COSTS_DETAIL,
	SUM(jopact.fsetupact) as SETP_COSTS_DETAIL,
	SUM(jopact.fothract) as OTHR_COSTS_DETAIL,
	SUM(jopact.flabact + 
		jopact.fmatlact + 
		jopact.fovhdact + 
		jopact.fsubact + 
		jopact.ftoolact + 
		jopact.fsetupact + 
		jopact.fothract) 
	as TOTAL_COSTS_DETAIL
from 
	jopact
	left join joitem on jopact.fjobno = joitem.fjobno
where 
	left(jopact.fjobno, 5) in (@JONO)
group by
	jopact.fjobno,
/* Incorrect syntax near the keyword 'as' here */
	joitem.fpartno) as DETAILS

ON LTRIM(RTRIM(RANKTABLE.JOBNO)) = DETAILS.JOBNO

ORDER BY
	RANKS.JobPath

Currently, that has me with a 'RootJob' is not a recognized table hints error, plus a couple of syntax errors. This is down from a slew of other errors that, taken together, indicate that I am pretty much out of my depth here.

So, before I scrap the idea and start over, I thought I'd chuck it up to the forums, on the off-chance that maybe it's something small. Can anyone make sense of this??

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 11:38:32

Your sql is malformed! The CTE needs to go at the start.

maybe this (can't test):


DECLARE @JONO as varchar(15)
SET @JONO = '20386'

;WITH Yak (RootJob, JobNo, JobPath, Indent)
AS (
	SELECT	RootJob,
		JobNo,
		CAST(JobNo AS VARCHAR(MAX)),
		0
	FROM	
		(select
			jomast.fjobno as JobNo,
			jomast.fschbefjob as RootJob
		from
			jomast
		where
			left(fjobno, 5) in (@JONO)) As QQQ
	
	WHERE	RootJob = ''
	UNION ALL
	SELECT		
		s.RootJob,
		s.JobNo,
		y.JobPath + ';' + s.JobNo,
		y.Indent + 1
	FROM
		Yak AS y
		INNER JOIN	
		(select
			jomast.fjobno as JobNo,
			jomast.fschbefjob as RootJob
		from
			jomast
		where
			left(fjobno, 5) in (@JONO)) AS s 
	ON s.RootJob = y.JobNo
)

SELECT
	REPLICATE(' ', 5 * yak.Indent) + yak.JobNo AS JobNo,
--	, RootJob
--	, Indent
	moreDetails.PARTNO,
	moreDetails.LABR_COSTS_DETAIL,
	moreDetails.MATL_COSTS_DETAIL,
	moreDetails.OVHD_COSTS_DETAIL,
	moreDetails.SUBS_COSTS_DETAIL,
	moreDetails.TOOL_COSTS_DETAIL,
	moreDetails.SETP_COSTS_DETAIL,
	moreDetails.OTHR_COSTS_DETAIL,
	moreDetails.TOTAL_COSTS_DETAIL
FROM
		Yak
		JOIN (
			select
				jopact.fjobno as JOBNO,
				joitem.fpartno as PARTNO,
				SUM(jopact.flabact) as LABR_COSTS_DETAIL,
				SUM(jopact.fmatlact) as MATL_COSTS_DETAIL,
				SUM(jopact.fovhdact) as OVHD_COSTS_DETAIL,
				SUM(jopact.fsubact) as SUBS_COSTS_DETAIL,
				SUM(jopact.ftoolact) as TOOL_COSTS_DETAIL,
				SUM(jopact.fsetupact) as SETP_COSTS_DETAIL,
				SUM(jopact.fothract) as OTHR_COSTS_DETAIL,
				SUM(jopact.flabact + 
					jopact.fmatlact + 
					jopact.fovhdact + 
					jopact.fsubact + 
					jopact.ftoolact + 
					jopact.fsetupact + 
					jopact.fothract) 
				as TOTAL_COSTS_DETAIL
			from 
				jopact
				left join joitem on jopact.fjobno = joitem.fjobno
			where 
				left(jopact.fjobno, 5) in (@JONO)
			group by
				jopact.fjobno,
				joitem.fpartno
		)
		moreDetails ON moreDetails.[jobNo] = yak.[jobNo]
ORDER BY	
		yak.JobPath

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-06-30 : 11:48:54

quote:
Originally posted by Transact Charlie

Your sql is malformed! The CTE needs to go at the start.

maybe this (can't test):
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Wow, that's pretty impressive - cut, paste, run, results. I don't know how you people do it (obviously) but I'm glad you do :)

Question (one of many) - what is the significance of using [] braces instead of just laying out the name in this line:

moreDetails ON moreDetails.[jobNo] = yak.[jobNo]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-30 : 12:18:30

That's just force of habit - personal taste. the square brackets are only actually necessary when you have a name that has a space, special character, or is a keyword.

For instance say I had this table


CREATE TABLE myTable (
    [COUNT] INT
    , [SOME TEXT] NVARCHAR(255)
    )

The I have to enclose the column names in [ ] because 1 is a keyword and the other has a space in it. Obviously names such as these are really stupid but you get the point...

I've just got into the habit of enclosing my columns this way (whether they need it or not) I find it makes it easier to scan my own code.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources