asked on
SQL BOM query (pt 2)
I posted a question last week about this and did get an answer that worked. but I need to find tune my query. I have a recursive query that gets my BOM however it is not indented. I see several items on lvl 0,1,2 however it does not put the levels in indented form. My query results show:
MainAssy SubAssy t_item t_qana lvl YearExp
4586-2 3761-4 A022202 1.69 2 1753
4586-2 4564 A023502 1.75 2 1753
4586-2 4586 3761-4 1 1 1753
4586-2 4586 4564 1 1 1753
4586-2 4586 4586 0 0 1753
4586-2 4586 4587 1 1 1753
4586-2 4587 A025602 2.75 2 1753
4586-2 64889 64889 1 0 1753
4586-2 64889 A021702 1.3 1 1753
The "A0" number are level 2 but A025602 in part 4587 and A023502 is in part 4564. This result does not indicate that.
Is there any way to rewrite the script so that it shows the levels in correct order meaning main, assy, sub assy, item etc.
Here is the script I am using now:
with BOMCTE AS (
SELECT t_mitm as MainAssy, t_sitm as SubAssy, t_sitm as t_item, t_qana, 0 AS lvl, year(t_exdt) as 'YearExp'
FROM ttibom010100
where year(t_exdt) = 1753
UNION ALL
SELECT usr.MainAssy, mgr.t_mitm, mgr.t_sitm, mgr.t_qana, usr.lvl +1 AS lvl, YearExp
FROM BOMCTE AS usr
INNER JOIN ttibom010100 AS mgr
ON usr.t_item = mgr.t_mitm
where year(t_exdt) = 1753
)
SELECT distinct *
FROM BOMCTE AS u
where MainAssy = '4586-2'
Thanks
MainAssy SubAssy t_item t_qana lvl YearExp
4586-2 3761-4 A022202 1.69 2 1753
4586-2 4564 A023502 1.75 2 1753
4586-2 4586 3761-4 1 1 1753
4586-2 4586 4564 1 1 1753
4586-2 4586 4586 0 0 1753
4586-2 4586 4587 1 1 1753
4586-2 4587 A025602 2.75 2 1753
4586-2 64889 64889 1 0 1753
4586-2 64889 A021702 1.3 1 1753
The "A0" number are level 2 but A025602 in part 4587 and A023502 is in part 4564. This result does not indicate that.
Is there any way to rewrite the script so that it shows the levels in correct order meaning main, assy, sub assy, item etc.
Here is the script I am using now:
with BOMCTE AS (
SELECT t_mitm as MainAssy, t_sitm as SubAssy, t_sitm as t_item, t_qana, 0 AS lvl, year(t_exdt) as 'YearExp'
FROM ttibom010100
where year(t_exdt) = 1753
UNION ALL
SELECT usr.MainAssy, mgr.t_mitm, mgr.t_sitm, mgr.t_qana, usr.lvl +1 AS lvl, YearExp
FROM BOMCTE AS usr
INNER JOIN ttibom010100 AS mgr
ON usr.t_item = mgr.t_mitm
where year(t_exdt) = 1753
)
SELECT distinct *
FROM BOMCTE AS u
where MainAssy = '4586-2'
Thanks
Microsoft SQL Server
Last Comment
Joe Grosskopf
The procs I provided on the other question lay out the data such that indenting them in your presentation layer is fairly simple using groups and some suppression. Did you attempt to use that? It's a lot different but shouldn't require much modification to work for your scenario.
ASKER
Thanks. I have tried that yet...was hoping a simple one line entry would fix my issue but I guess I need to bite-the-bullet and try setting up the script using what you sent. I'll do that and report back in a day or two. Thanks Again
ASKER
I am entering the BOM procedure and making changes as needed. 2 questions if you do not mind.
1.) The script references table ttcibd001100. In c4, we do not have (or at least we do not use that table). Can you tell em what that table is? Is it the Item Master? I would like to change to the correct c4 table
2.) After entering the procedure and running it, the variables are itemcsv, item1 and item2. I believe itemcsv is the sepeerator I wish to use but I do not understand the item1 and item2. I thought I would enter 1 item number and get teh BOM for that one item
1.) The script references table ttcibd001100. In c4, we do not have (or at least we do not use that table). Can you tell em what that table is? Is it the Item Master? I would like to change to the correct c4 table
2.) After entering the procedure and running it, the variables are itemcsv, item1 and item2. I believe itemcsv is the sepeerator I wish to use but I do not understand the item1 and item2. I thought I would enter 1 item number and get teh BOM for that one item
Yes, ttcibd001100 is the item master (for logistics company 100).
item 1 and item 2 is a means to allow a range of items. Item1 = 10000, Item2 = 10004 will return data for 10000, 10001, 10002, 10003, 10004.
To do just a single item, set the itemcsv = '10000' (or enter 10000 in both item1 and item2). You could also just set item2 = item1 when item2 is null or blank.
My customers often have multiple items in a tight sequence so the item range option is convenient in those cases.
item 1 and item 2 is a means to allow a range of items. Item1 = 10000, Item2 = 10004 will return data for 10000, 10001, 10002, 10003, 10004.
To do just a single item, set the itemcsv = '10000' (or enter 10000 in both item1 and item2). You could also just set item2 = item1 when item2 is null or blank.
My customers often have multiple items in a tight sequence so the item range option is convenient in those cases.
ASKER
Thanks again for working with me on this. I did what you suggested....
USE [baanivc4db]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[BOM_10Levels]
@ItemCSV = N'8730',
@Item1 = N'',
@Item2 = N''
SELECT 'Return Value' = @return_value
GO
Hoping to get the BOM for item 8730
I get:
KeyItem KeyItemLevel ImmediateParent TopLevelParent Level1Child Level2Child Level3Child Level4Child Level5Child Level6Child Level7Child Level8Child Level9Child Level10Child
8730 0 NULL 8730 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
and
Return Value
0
Do you have any idea what I am missing? or what part of the SP I forgot to change for c4?
USE [baanivc4db]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[BOM_10Levels]
@ItemCSV = N'8730',
@Item1 = N'',
@Item2 = N''
SELECT 'Return Value' = @return_value
GO
Hoping to get the BOM for item 8730
I get:
KeyItem KeyItemLevel ImmediateParent TopLevelParent Level1Child Level2Child Level3Child Level4Child Level5Child Level6Child Level7Child Level8Child Level9Child Level10Child
8730 0 NULL 8730 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
and
Return Value
0
Do you have any idea what I am missing? or what part of the SP I forgot to change for c4?
It looks like in BOM_10Levels, the link to get the child records is not returning anything.
In 5C and LN, the item is a combined field consisting of a 9 character project section and then the item code.
First place to check would be the initial insert into @ERPBOM. In LN, they stopped using the 1970 epoch for the current BOM records so I only look for a future date. Try with it like this:
"AND (t_exdt > GETDATE() OR t_exdt = '1970-01-01')" instead of "t_exdt > GETDATE()"
DECLARE @ERPBOM Table (
t_mitm varchar(50),
t_sitm varchar(50)
)
INSERT INTO @ERPBOM (t_mitm, t_sitm)
SELECT t_mitm, t_sitm
FROM ttibom010100
WHERE t_indt < GETDATE()
AND t_exdt > GETDATE()
In 5C and LN, the item is a combined field consisting of a 9 character project section and then the item code.
First place to check would be the initial insert into @ERPBOM. In LN, they stopped using the 1970 epoch for the current BOM records so I only look for a future date. Try with it like this:
"AND (t_exdt > GETDATE() OR t_exdt = '1970-01-01')" instead of "t_exdt > GETDATE()"
DECLARE @ERPBOM Table (
t_mitm varchar(50),
t_sitm varchar(50)
)
INSERT INTO @ERPBOM (t_mitm, t_sitm)
SELECT t_mitm, t_sitm
FROM ttibom010100
WHERE t_indt < GETDATE()
AND t_exdt > GETDATE()
ASKER
That is not the case. I changed it to:
DECLARE @ERPBOM Table (
t_mitm varchar(50),
t_sitm varchar(50)
)
INSERT INTO @ERPBOM (t_mitm, t_sitm)
SELECT t_mitm, t_sitm
FROM ttibom010100
WHERE year(t_exdt) = 1753
If I simply run that, I get 203070 rows affected. This tells me if it getting thh data but the link between what I am asking for and this 203,000 records is not happening.
DECLARE @ERPBOM Table (
t_mitm varchar(50),
t_sitm varchar(50)
)
INSERT INTO @ERPBOM (t_mitm, t_sitm)
SELECT t_mitm, t_sitm
FROM ttibom010100
WHERE year(t_exdt) = 1753
If I simply run that, I get 203070 rows affected. This tells me if it getting thh data but the link between what I am asking for and this 203,000 records is not happening.
Ok, that's good. @ERPBOM holds all the active parent/child relationships in the entire BOM table.
Please double-check that @Items and @BOM have records. @Items holds the top-level parent items from the parameter inputs. @BOM is like a seed table, prior to the loop, it should hold one record for each record in the @Items table.
I started to mention the 9 spaces that precedes the item code in LN. Does that exist in your Baan? If not, then you'll need to get rid of that in the section that parses @ItemCSV (and where @Item1 and @Item2 are tweaked).
Please double-check that @Items and @BOM have records. @Items holds the top-level parent items from the parameter inputs. @BOM is like a seed table, prior to the loop, it should hold one record for each record in the @Items table.
I started to mention the 9 spaces that precedes the item code in LN. Does that exist in your Baan? If not, then you'll need to get rid of that in the section that parses @ItemCSV (and where @Item1 and @Item2 are tweaked).
ASKER
Now we are getting somewhere...it was the 9 spaces. Now I have a lot of results and I need to see how to translate that to get a report that my users can use.
Thanks
Thanks
Excellent.
The way I have laid it out to visually show the depth is to do a group by the Top Level Parent.
Then in the details section, I lay out level1Child, level2Child, Level3Child... going from left to right. Then for each of those fields, I conditionally suppress it from view. So for level1child, it is suppressed when level2child is not null
Level2Child is suppressed when Level3Child is not null and so on. I've attached a couple screenshots to help explain this.
BOM-report-example.pdf
The way I have laid it out to visually show the depth is to do a group by the Top Level Parent.
Then in the details section, I lay out level1Child, level2Child, Level3Child... going from left to right. Then for each of those fields, I conditionally suppress it from view. So for level1child, it is suppressed when level2child is not null
Level2Child is suppressed when Level3Child is not null and so on. I've attached a couple screenshots to help explain this.
BOM-report-example.pdf
ASKER
K, Thanks again. I know once I get this one they are also going to want one that shows customized items so I'll have to incorporate the project number and project table. Hopefully, it is just a matter of changing the table then changing one of the @Item fields to point to the project number....do you think that will work?
ASKER
I need the qty of each item. I added it here:
INSERT INTO @ERPBOM (t_mitm, t_sitm, t_qana)
SELECT t_mitm, t_sitm, t_qana
FROM ttibom010100
WHERE year(t_exdt) = 1753
but how do I get the t_qana to show up in my level list? or is this something you do once it hits the report?
INSERT INTO @ERPBOM (t_mitm, t_sitm, t_qana)
SELECT t_mitm, t_sitm, t_qana
FROM ttibom010100
WHERE year(t_exdt) = 1753
but how do I get the t_qana to show up in my level list? or is this something you do once it hits the report?
I'm not exactly sure what will need to change work with projects. If a project has a list of items, then you can take the project number as a parameter, pull all the associated items and create a comma-separated string and then pass that to the BOM_10Levels proc.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
So far this is working great. Just wondering do you have any suggestion how to make the project number a variable? I was thinking of leaving Item1 and Item2 as is and changing itemCSV to project number. But looking through the script, i cannot see where I can tell itemCSV to match up to t_cprj. I already change the table to the customized BOM and added t_cprj as a variable, just need to query it. Thanks
What is the relationship between a project number and a bill of material?
ASKER
Custom Bills of material are created for projects. I think I actually got it. Instead of changing the ItemCSV, I just added a project number variable and then added the @Project = t_cprj to the "where" clause. I have an engineer checking it now. Thanks so much for this and your help.
ASKER
Very good solution. Helped with an issue we've been having for a long time