PDA

View Full Version : Store Procedure that deals with nested tables?



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!!!

stanl
11-19-2010, 06:19 AM
You didn't mention what your DB was; but if SQL Server, I would consider setting up output from the report services.

deyken
11-19-2010, 07:04 AM
Hi Stanl,

Yes it is SQL SERVER. How would I set up output from Report services?

deyken
11-21-2010, 11:52 PM
Hi All,

OK Below is the Query code I attempted (SQL Server 2008 Express) so far. I figured that perhaps being able to Group By (SITE_CODE) may deliver the collective result set that I could work with. Below I list the SQL Statement and below that the error I am getting when attempting to run the query in SQL Management Studio's interface:

[SQL START]
SELECT tblBOM_Detail.PRODUCT_CODE AS [Product #], tblBOM_Detail.PRODUCT_DESCRIPTION AS [Description], tblBOM_SITES.SITE_NAME AS [Site Name], tblBOM_Detail.NETT_QTY
FROM tblBOM_Detail INNERJOIN
tblBOM_Master ON tblBOM_Detail.BOM_NO = tblBOM_Master.BOM_NO INNERJOIN
tblBOM_SITES ON tblBOM_Detail.SITE_CODE = tblBOM_SITES.SITE_CODE AND tblBOM_Master.BOM_NO = tblBOM_SITES.BOM_NO
WHERE (tblBOM_Detail.BOM_NO ='9') //PS: this will become a paramenter @BomNo for later use
groupby tblBOM_SITES.SITE_CODE
[SQL END]



{SQL Error Message Start}
Msg 8120, Level 16, State 1, Line 1
Column 'tblBOM_Detail.PRODUCT_CODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
{SQL Error Message End}

Any suggestions?

Norie
11-26-2010, 09:09 PM
Shouldn't groupby actually be GROUP BY?

ie 2 words

As for the design of your interface - it's probably possible but it'll take a lot of work.

It might be an idea to rethink it, having a separate column for each site just doesn't sound quite right to me.

If you do need some sort of parent/detail setup how about having a list of sites in the parent 'view', with some details for identification.

Then some sort of link, eg button, that displays all the data for the site the user has chosen in a details 'view'.

There could add further functionality to the details view to allow the user to add/amend/delete data and you could do the same for the parent view too.

I've set up similar 'interfaces' using ASP where quite a bit of the work is done for you.

I don't know about VS2008, but in VS2010 I'm sure you can set up something like this.

You could probably do it in Excel VBA, perhaps using ADO, too but you'd have to code a lot of the functionality yourself.