August 1, 2008 at 11:41 am
We recently implemented a new ERP system and for various reasons I need to write my own version of a standard system report that generates an indented bill of materials (BOM) report. The BOM is represented as a set of records in the PartMtl table that identifies the parent part, the sequence number (10,20,30...), and the child part.
I can use a CTE to build this query and get all of the parts underneath a top-level assembly. The problem is how to sort the data so it shows up in the report (ultimately Crystal Reports) in the correct order.
My CTE looks like this:
DECLARE @TOPPART VARCHAR(32)
SET @TOPPART = '11001-01';
WITH BOM(partnum,revisionnum,mtlseq,mtlpartnum,qtyper,bomlevel,sortkey)
AS
(
SELECT partnum,revisionnum,mtlseq,mtlpartnum,qtyper,1 as bomlevel,
CONVERT(varchar(1024), mtlseq) from partmtl where partnum = @TOPPART
UNION ALL
SELECT pm.partnum,pm.revisionnum,pm.mtlseq,pm.mtlpartnum,pm.qtyper,bomlevel + 1,
CONVERT(varchar(1024), sortkey + '.' + CONVERT(varchar(1024),pm.mtlseq)) from partmtl pm
INNER JOIN BOM b ON pm.partnum = b.mtlpartnum
)
SELECT partnum,revisionnum,mtlseq,mtlpartnum,qtyper,bomlevel,sortkey from BOM order by sortkey;
Sortkey contains the full path in the BOM tree for each component part based on its sequence number (e.g. "10.10", "100.20", etc.) The problem with this approach is that Sortkey is going to sort alphabetically, which puts 100.20 before 30.10, and so on.
I've considered using some sort of numeric-based scheme, but this has place-value issues. There must be a more elegant approach that I'm missing. This seems like a common thing to do.
Can someone clever point me in the right direction?
August 1, 2008 at 12:25 pm
Include numeric sort fields in the CTE, you don't have to output them to the report. LIke this:
[font="Courier New"]DECLARE @TOPPART VARCHAR(32)
SET @TOPPART = '11001-01';
WITH BOM(partnum,revisionnum,mtlseq,mtlpartnum,qtyper,bomlevel,sortkey, sortkeynum1, sortkeynum2)
AS
(
SELECT partnum,revisionnum,mtlseq,mtlpartnum,qtyper,1 AS bomlevel,
CONVERT(VARCHAR(1024), mtlseq), mtlseq, 0 FROM partmtl WHERE partnum = @TOPPART
UNION ALL
SELECT pm.partnum,pm.revisionnum,pm.mtlseq,pm.mtlpartnum,pm.qtyper,bomlevel + 1,
CONVERT(VARCHAR(1024), sortkey + '.' + CONVERT(VARCHAR(1024),pm.mtlseq)), sortkey, pm.mtlseq FROM partmtl pm
INNER JOIN BOM b ON pm.partnum = b.mtlpartnum
)
SELECT partnum,revisionnum,mtlseq,mtlpartnum,qtyper,bomlevel,sortkey FROM BOM ORDER BY sortnum1, sortkeynum2;[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 12:29 pm
Thanks Jack. I'm getting this error with your query:
Msg 240, Level 16, State 1, Line 5
Types don't match between the anchor and the recursive part in column "sortkeynum1" of recursive query "BOM".
August 1, 2008 at 12:34 pm
Yeah, that would make sense as SortKey is mtlseq converted to varchar. You would just want to convert SortKey back to a numeric.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 1, 2008 at 12:43 pm
The problem is that I can't see how any direct sort of my CTE data is going to produce the results I want, which is a fully-expanded indented tree in order of the mtlseq numbers, with the sub-assembly mtlseq records in the right place.
I'm having a brain fart here as I'm just not seeing it.
August 1, 2008 at 1:35 pm
Brian:
There are a couple of ways to do this. One is to left-fill the individual keys before the get added into the overall sortkey. So instead of:
100.10
30.20
you would have:
0030.0020
0100.0010
In order for this to work, you have to know what the maximum length any single key will be when converted to varchar, and then fill all of the keys to that size. I have assumed 4 digits here (max key=9999).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 1, 2008 at 2:15 pm
Yes! That's what I wasn't seeing. I couldn't find an LPAD() function in T-SQL so I did this:
DECLARE @TOPPART VARCHAR(32)
DECLARE @COMPANY VARCHAR(8)
DECLARE @PADDING INT
DECLARE @INDENTCHAR CHAR(1)
SET @TOPPART = '11011-01';
SET @COMPANY = 'PICO';
SET @PADDING = 8;
SET @INDENTCHAR = '.';
WITH BOM(partnum,indentedmtlpartnum,revisionnum,assy,partdesc,mtlseq,mtlseqpad,mtlpartnum,qtyper,bubblenum,bomlevel,sortkey)
AS
(
SELECT pm1.partnum,CONVERT(varchar(1024),REPLICATE(@INDENTCHAR,1) + pm1.mtlpartnum),pm1.revisionnum,p1.method,p1.partdescription,pm1.mtlseq,
REPLICATE('0',@PADDING - LEN(CAST(pm1.mtlseq AS VARCHAR))) + CAST(pm1.mtlseq AS VARCHAR),
pm1.mtlpartnum,pm1.qtyper,
pm1.bubblenum,1 as bomlevel,
CONVERT(varchar,REPLICATE('0',@PADDING - LEN(CAST(pm1.mtlseq AS VARCHAR))) + CAST(pm1.mtlseq AS VARCHAR))
FROM partmtl pm1
INNER JOIN part p1 ON p1.partnum = pm1.mtlpartnum
WHERE pm1.partnum = @TOPPART AND pm1.company = @COMPANY AND p1.company = @COMPANY
UNION ALL
SELECT pm2.partnum,CONVERT(varchar(1024),REPLICATE(@INDENTCHAR,bomlevel+1) + pm2.mtlpartnum),pm2.revisionnum,p2.method,p2.partdescription,pm2.mtlseq,
REPLICATE('0',@PADDING - LEN(CAST(pm2.mtlseq AS VARCHAR))) + CAST(pm2.mtlseq AS VARCHAR),
pm2.mtlpartnum,pm2.qtyper,
pm2.bubblenum,bomlevel + 1,
CONVERT(varchar, sortkey + '.' + REPLICATE('0',@PADDING - LEN(CAST(pm2.mtlseq AS VARCHAR))) + CAST(pm2.mtlseq AS VARCHAR))
FROM partmtl pm2
INNER JOIN part p2 ON p2.partnum = pm2.mtlpartnum
INNER JOIN BOM b ON pm2.partnum = b.mtlpartnum
WHERE pm2.company = @COMPANY and p2.company = @COMPANY
)
SELECT partnum,indentedmtlpartnum,revisionnum,partdesc,assy,mtlseq,mtlseqpad,mtlpartnum,qtyper,bubblenum,bomlevel,sortkey from BOM
ORDER BY sortkey ASC;
This produces identical results to the system-generated report for the parts I tested. Woo hoo! 🙂
It is ugly, but my T-SQL is rather rusty.
Thanks for the help!
August 1, 2008 at 3:26 pm
Thanks for the feedback, Brian
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 19, 2008 at 2:43 am
Thanks for the thread guys, this has just helped me out too 🙂
Jim
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply