May 13, 2013 at 1:19 pm
There are 5 tables we have which has a parent child relation we have following tables.
Order (Fields Order#, RefType, RefNum, RefLine)
OrderLine (Fields OrderLine, RefType, RefNum, RefLine)
Job (Fields JobNum, JobLine, RefType, RefNum, RefLine)
POLine (Fields PONum, POLine, RefType, RefNum, RefLine)
TransferLIne (Fields TONum, TOLine, RefType, RefNum, RefLine)
All the Tables ar related to each other using 3 fields 1.RefType 2.RefNum 3.RefLine.
RefType is either 'Order', 'OrderLine', 'Job', POLine' , 'TransferLine'
We would like to present the information something like below in the Treeview in Vb.net application using Stored Proc .
Order#
- OrderLine
-- Job
--- POLine
----Job
------ Order
- OrderLine
-- Job
--- POLine
----Job
------ Order
I am also attaching the screen shot of how the program should look like.
Here is the link to the image file.
May 13, 2013 at 1:53 pm
skb 44459 (5/13/2013)
There are 5 tables we have which has a parent child relation we have following tables.Order (Fields Order#, RefType, RefNum, RefLine)
OrderLine (Fields OrderLine, RefType, RefNum, RefLine)
Job (Fields JobNum, JobLine, RefType, RefNum, RefLine)
POLine (Fields PONum, POLine, RefType, RefNum, RefLine)
TransferLIne (Fields TONum, TOLine, RefType, RefNum, RefLine)
All the Tables ar related to each other using 3 fields 1.RefType 2.RefNum 3.RefLine.
RefType is either 'Order', 'OrderLine', 'Job', POLine' , 'TransferLine'
We would like to present the information something like below in the Treeview in Vb.net application using Stored Proc .
Order#
- OrderLine
-- Job
--- POLine
----Job
------ Order
- OrderLine
-- Job
--- POLine
----Job
------ Order
I am also attaching the screen shot of how the program should look like.
Here is the link to the image file.
I am guessing that the question is something like "How do I create a stored procedure that will display the hierarchy for a given (not sure what the root is here)?"
Based on the lack of details the best answer you are likely to get is "with a recursive cte".
If that answer is enough then I am glad I could help. However, I suspect you probably would like a bit more detailed assistance than that. I am happy to help but first you need to help me. You will need to post ddl (create table statements), sample data (insert statements) and desired output based on your sample data.
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2013 at 6:57 am
Here is the Data Defination and Data and how the tree should look like. What format the stored procedure should give the output data so it can be used in TreeView in stored Procedure ?
CREATE TABLE CO( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE COLine( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE Job( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE Lot( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
CREATE TABLE POLine( Module nvarchar(20), KeyValue nvarchar(100), KeyLine int, RefModule nvarchar(20), RefValue nvarchar(100), RefLine int)
insert into CO values ( 'CO', 'KB11-02916' , 0, null , null , null)
insert into COLine values ( 'COLine', 'KB11-02916' , 1, 'CO' , 'KB11-02916' , 0)
insert into Job values ( 'Job', 'J000503035' , 0, 'COLine' , 'KB11-02916' , 1)
insert into Job values ( 'Job', 'J000503035' , 1, 'Job' , 'J000503035' , 0)
insert into Lot values ( 'Lot', 'LOTJ5030350000' , 0, 'Job' , 'J000503035' , 1)
insert into POLine values ( 'POLine', 'PH00022127' , 1, 'Job' , 'J000503035' , 1)
insert into POLine values ( 'POLine', 'PH00022127' , 2, 'Job' , 'J000503035' , 1)
insert into Job values ( 'Job', 'J000503035' , 3, 'Job' , 'J000503035' , 1)
insert into POLine values ( 'POLine', 'PH00022137' , 1, 'Job' , 'J000503035' , 3)
insert into POLine values ( 'POLine', 'PH00022137' , 2, 'Job' , 'J000503035' , 3)
insert into LOT values ( 'LOT', 'PHL13938' , 0, 'POLine' , 'PH00022127' , 1)
insert into LOT values ( 'LOT', 'PHL13939' , 0, 'POLine' , 'PH00022127' , 1)
insert into LOT values ( 'LOT', 'PHL13940' , 0, 'POLine' , 'PH00022127' , 2)
insert into LOT values ( 'LOT', 'PHL13941' , 0, 'POLine' , 'PH00022127' , 2)
insert into LOT values ( 'LOT', 'PHL13100' , 0, 'POLine' , 'PH00022137' , 1)
insert into LOT values ( 'LOT', 'PHL13101' , 0, 'POLine' , 'PH00022137' , 1)
insert into LOT values ( 'LOT', 'PHL13102' , 0, 'POLine' , 'PH00022137' , 2)
Here how the tree should look like
CO KB11-02916
-COLine KB11-02916-1
--Job J000503035-0
---Job J000503035-1
----Lot LOTJ5030350000-0
----POLine PH00022127-1
-----Lot PHL13938-0
-----Lot PHL13939-0
----POLine PH00022127-2
-----Lot PHL13940-0
-----Lot PHL13941-0
----Job J000503035-3
-----POLine PH00022137-1
------Lot PHL13100-0
------Lot PHL13101-0
-----POLine PH00022137-2
------Lot PHL13102-0
------Lot PHL13103-0
June 3, 2013 at 7:36 am
What is the relationship between these tables? This sort of looks like a hybrid EAV. Do you have to look in the RefModule column in each table to know which table it relates to?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 3, 2013 at 8:23 am
The realtionship is defined using three fields RefModule, RefNum, RefLine.
The RefModule field is in all the tables , thats how it determines which table to look for.
FYI :
The actual design of the tables are different. I just simplified the design to acheive the goal.
For example : the POLine table has fields like po_num, po_line, ref_module, ref_num, ref_line
The Job table has fields like job, suffix, ref_module, ref_num, ref_line.
June 4, 2013 at 12:38 pm
Any Luck ?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply