deyken
11-18-2010, 11:06 PM
Hi All,
I am in a bind! I have a 3-tiered relationship that looks like this:
1st Tier Master Table: Bill of Materials Header, one-to-many on
2nd Tier Master: Bill of Materials Sites, one-to-many on
3rd Tier Detail: Bill of Materials Items
The Master table PK = BOM_NO
I put the BOM_NO into the Sites Table as a FK (SITE_CODE = PK for this table)
I then put both the BOM_NO and the SITE_CODE into the Items (detail) table as Foreign Keys.
The relationship works beautifully, however, my Engineering department now threw a little spanner into the works:
They want an interface (be it Excel or 3rd Party VS 2008 front end) that displays the header once only to Capture a New Bill of Materials (BOM). Then they will add the individual Sites for this BOM on the second tier table. Once we get to each individual Site's Items, they want the Items to display as a list with a column for each Site on the Detail Table's interface. So the Detail table needs to include (in the result set and/or on the front end) both the Detail records as well as the 2nd Tier Master records in the same result set as a grid. This grid can then be diplayed on a Grid control or in an Excel worksheet...
I do not have any working Code to display here, but this is what I have tried so far:
1. I looped through the 2nd Tier table to ascertain # of Sites and to catch the Site Names (SITE_NAME). This is a stand-alone recordset for now, grabbed using the DISTINCT command (I only need one instance of each Site Name, obviously)
2. Then I tried adding the Sites' Names as individual Columns on the interface (this works to a certain extent in VS 2008, Excel etc.)
3. I then produced a DISTINCT Recordset grabbed from the Detail table (SQL SERVER 2008) and displayed this in the interface.
4. What now needs to happen is the Quantity of each individual Item, as it pertains to each individual Site, now needs to be displayed in each Site Column to be editable by the Engineer at a later stage. Otherwise the Engineer has to do this for all the items (most of which are diplicates) for each individual site...
How do I accomplish this? Is there a way to write a compact SP that could produce a Result Set as a return Variable, where this result set is such a nested table.
Here is what I think (please excuse the bad coding, I have ZERO experience on stored procedures...):
CREATE PROCEDURE hp_BOM_SITE_QUANTITIES
@BomNo int,
@SiteCode nvarchar(50),
AS
BEGIN
SELECT * FROM tblBOM_MASTER
WHERE BOM_NO = @BomNo
SELECT DISTINCT SITE_NAME from tblBOM_SITES
WHERE SITE_CODE = @SiteCode
SELECT DISTINCT PRODUCT_DESCRIPTION, NETT_QTY from tblBOM_DETAIL
WHERE SITE_CODE = @SiteCode
AND BOM_NO = @BomNo
END
I have no idea to create a return (output) variable or how to access it from outside SQL SERVER 2008, nor do I have any way of ascertaining whether my code above actually could work...
Any help would be GREATLY appreciated!!!
I am in a bind! I have a 3-tiered relationship that looks like this:
1st Tier Master Table: Bill of Materials Header, one-to-many on
2nd Tier Master: Bill of Materials Sites, one-to-many on
3rd Tier Detail: Bill of Materials Items
The Master table PK = BOM_NO
I put the BOM_NO into the Sites Table as a FK (SITE_CODE = PK for this table)
I then put both the BOM_NO and the SITE_CODE into the Items (detail) table as Foreign Keys.
The relationship works beautifully, however, my Engineering department now threw a little spanner into the works:
They want an interface (be it Excel or 3rd Party VS 2008 front end) that displays the header once only to Capture a New Bill of Materials (BOM). Then they will add the individual Sites for this BOM on the second tier table. Once we get to each individual Site's Items, they want the Items to display as a list with a column for each Site on the Detail Table's interface. So the Detail table needs to include (in the result set and/or on the front end) both the Detail records as well as the 2nd Tier Master records in the same result set as a grid. This grid can then be diplayed on a Grid control or in an Excel worksheet...
I do not have any working Code to display here, but this is what I have tried so far:
1. I looped through the 2nd Tier table to ascertain # of Sites and to catch the Site Names (SITE_NAME). This is a stand-alone recordset for now, grabbed using the DISTINCT command (I only need one instance of each Site Name, obviously)
2. Then I tried adding the Sites' Names as individual Columns on the interface (this works to a certain extent in VS 2008, Excel etc.)
3. I then produced a DISTINCT Recordset grabbed from the Detail table (SQL SERVER 2008) and displayed this in the interface.
4. What now needs to happen is the Quantity of each individual Item, as it pertains to each individual Site, now needs to be displayed in each Site Column to be editable by the Engineer at a later stage. Otherwise the Engineer has to do this for all the items (most of which are diplicates) for each individual site...
How do I accomplish this? Is there a way to write a compact SP that could produce a Result Set as a return Variable, where this result set is such a nested table.
Here is what I think (please excuse the bad coding, I have ZERO experience on stored procedures...):
CREATE PROCEDURE hp_BOM_SITE_QUANTITIES
@BomNo int,
@SiteCode nvarchar(50),
AS
BEGIN
SELECT * FROM tblBOM_MASTER
WHERE BOM_NO = @BomNo
SELECT DISTINCT SITE_NAME from tblBOM_SITES
WHERE SITE_CODE = @SiteCode
SELECT DISTINCT PRODUCT_DESCRIPTION, NETT_QTY from tblBOM_DETAIL
WHERE SITE_CODE = @SiteCode
AND BOM_NO = @BomNo
END
I have no idea to create a return (output) variable or how to access it from outside SQL SERVER 2008, nor do I have any way of ascertaining whether my code above actually could work...
Any help would be GREATLY appreciated!!!