June 19, 2009 at 1:29 pm
I am doing a very simple query, but the result set I am getting is not what I am expecting.
(NOTE: I am aware that these table names and field names have hyphens, and I am very aware that that's an unwise thing to do; I didn't play a part in naming them, and we are stuck with them).
Only two tables. One (we'll call this one Table 1 for this discussion) has 4 columns:
CREATE TABLE [dbo].[BATCH-REC-HDR](
[Batch-Num] [int] NULL,
[Batch-Warehouse] [varchar](16) NULL,
[Prod-Pkg-Code] [varchar](34) NULL,
[Quantity-Manufactured] [numeric](19, 4) NULL
The other has 8 fields:
CREATE TABLE [dbo].[BATCH-REC-LABOR](
[Batch-Num] [int] NULL,
[Labor-Code] [varchar](24) NULL,
[Used-Time] [numeric](17, 2) NULL,
[Used-Time-UM] [varchar](8) NULL,
[Cost-UM] [varchar](8) NULL,
[Cost] [numeric](19, 4) NULL,
[Extended-Cost] [numeric](17, 2) NULL,
[Created-date] [datetime] NULL
As you can guess, Batch-Num is the field I am joining on. It is unique in the first table, so for each Batch-Num in table 1, there are many in table 2.
So I want to do a select from both tables, joining where table1.batch-num = table2.batch-num:
SELECT [BATCH-REC-HDR].[Batch-Num], [BATCH-REC-HDR].[Batch-Warehouse], [BATCH-REC-HDR].[Prod-Pkg-Code], [BATCH-REC-HDR].[Quantity-Manufactured],
[BATCH-REC-LABOR].[Labor-Code], [BATCH-REC-LABOR].[Used-Time], [BATCH-REC-LABOR].[Used-Time-UM], [BATCH-REC-LABOR].[Cost-UM],
[BATCH-REC-LABOR].Cost, [BATCH-REC-LABOR].[Extended-Cost], [BATCH-REC-LABOR].[Created-date]
FROM [BATCH-REC-HDR] INNER JOIN
[BATCH-REC-LABOR] ON [BATCH-REC-HDR].[Batch-Num] = [BATCH-REC-LABOR].[Batch-Num]
WHERE [BATCH-REC-HDR].[Batch-Warehouse] IN ('02-01', '03-01', '05-01')
Even though I am sure that the [Batch-Num] field in table1 is unique per row, I am getting a huge results set in which the [Batch-Num] is repeated often:
(first few rows)
668102-01R8310000-20F 2.000002- Tech10.00minhr14.00002.332003-02-14 00:00:00.000
668103-01RMLM2600-500G 48.000002- Tech10.00minhr14.00002.332003-02-14 00:00:00.000
668105-01RSOG0020-500A 12.000002- Tech10.00minhr14.00002.332003-02-14 00:00:00.000
I will play around with this some more, but was hoping someone might see what I am missing.
(PS I am a web developer thrust into the role of DBA, so be gentle)
June 19, 2009 at 1:38 pm
That's the way a join works. Even though you may only have one batch number record in table 1, if table 2 has three records with that number, then it will show three records in your result set. If you're looking for something more like one batch number in the result set you may want to try doing a GROUP BY statement to roll it up or use the DISINCT clause to eliminate duplicates. Post back here if you have some more questions on how to set that up.
Cheers,
Brian
June 19, 2009 at 1:43 pm
What I need is actual labor per product per batch receipted per warehouse.
I tried GROUP BY, but it tells me to basically put every field in the GROUP BY which is in the SELECT section. I'm having a hard time wrapping my head around how that will help narrow this down.
What I need is actual labor per product per batch receipted per warehouse.
June 19, 2009 at 1:48 pm
I tried adding a DISTINCT according to what I read in BOL, but I guess I don't get it either. It's in the second field in the first line below:
SELECT [BATCH-REC-HDR].[Batch-Num], DISTINCT [BATCH-REC-HDR].[Batch-Warehouse], [BATCH-REC-HDR].[Prod-Pkg-Code], [BATCH-REC-HDR].[Quantity-Manufactured],
[BATCH-REC-LABOR].[Labor-Code], [BATCH-REC-LABOR].[Used-Time], [BATCH-REC-LABOR].[Used-Time-UM], [BATCH-REC-LABOR].[Cost-UM],
[BATCH-REC-LABOR].Cost, [BATCH-REC-LABOR].[Extended-Cost], [BATCH-REC-LABOR].[Created-date]
FROM [BATCH-REC-HDR] FULL OUTER JOIN
[BATCH-REC-LABOR] ON [BATCH-REC-HDR].[Batch-Num] = [BATCH-REC-LABOR].[Batch-Num]
WHERE ([BATCH-REC-HDR].[Batch-Warehouse] IN ('02-01', '03-01', '05-01'))
AND [Created-Date] > '1/1/2008'
The message I get just says there's an error near the word DISTINCT. Doesn't say what the error is.
June 19, 2009 at 2:05 pm
Because of this statement:
What I need is actual labor per product per batch receipted per warehouse.
I will probably have to do some math. Then I'll do a GROUP BY.
thanks
June 19, 2009 at 2:50 pm
The translation of your requirement sounds like:
SUM(actual labor) GROUP BY batch, warehouse.
The actual labor cost is not directly stored in You're also mentioning a product ("labor per product").
If this is represented by [Prod-Pkg-Code] then you'd need to add that column to your GROUP By statement as well.
When doing select statements with GROUP BY you should consider to include only the columns that are required for the result set and not all that are available.
Basically you should have a statement like
SELECT SUM(actual labor), [BATCH-REC-HDR].[Batch-Num], [BATCH-REC-HDR].[Batch-Warehouse] FROM [BATCH-REC-HDR] FULL OUTER JOIN
[BATCH-REC-LABOR] ON [BATCH-REC-HDR].[Batch-Num] = [BATCH-REC-LABOR].[Batch-Num]
WHERE ([BATCH-REC-HDR].[Batch-Warehouse] IN ('02-01', '03-01', '05-01'))
AND [Created-Date] > '1/1/2008'
GROUP BY [BATCH-REC-HDR].[Batch-Num], [BATCH-REC-HDR].[Batch-Warehouse]
June 19, 2009 at 2:53 pm
that makes a lot of sense. Let me see what I can do with that. Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply