April 18, 2012 at 4:52 am
Hi
I am hoping for a little guidance from someone with more knowledge on subqueries than myself.
I have a table where all items are scanned out of the factory and a flag that is marked if they are returned.
In the attached file order number 2643040 is the example. I know that 8 items were scanned out of the factory and that 4 were returned.
I am only interested in the items that were returned, but I always get the 8 rows!!!! I have tried writting queries with and without subqueries, but the results are the same.
The attached file creates a small test database with test data for the above order and both the queries I have tried.
Thanks in advance for any help I am a bit puzzled
April 18, 2012 at 6:02 am
ok first thing is congrats on posting the ddl and sample data. here are the queries posted for a quick glance by every one.
/* original query without a subquery to summarise the 4 items returned */
SELECT COUNT(PROD_PROD_BARCODES.MATERIAL_ID) AS CountOfMATERIAL_ID, PROD_PROD_BARCODES.REASON_CODE,
PROD_ASSEMBLY_BARCODES.SCAN_DESPATCHED_DATE, PROD_ASSEMBLY_BARCODES.SCAN_RETURNED_DATE,
PROD_PROD_BARCODES.OUTCOME, PROD_ASSEMBLY_BARCODES.MATERIAL_ID, PROD_ASSEMBLY_BARCODES.DETAILS,
PROD_ASSEMBLY_BARCODES.ACCOUNT, PROD_ASSEMBLY_BARCODES.NAME, SOP_RESCHEDULES.ID, SOP_RESCHEDULES.ID_DESCRIPTION,
PROD_ASSEMBLY_BARCODES.ORDER_NO, SOP_CUST_SERVICE_HEADER.RESPONSIBILITY, PROD_ASSEMBLY_BARCODES.LOAD_NO,
PROD_PROD_BARCODES.OUTCOME AS Expr1, PROD_PROD_BARCODES.CREATION_DATE, STOCK_ITEMS.STK_FAMILY, SOP_CUSTOMER.INV_TO,
SOP_DAMAGE_CODES.DESCRIPTION AS DAMAGE_CODE
FROM SOP_DAMAGE_CODES INNER JOIN
SOP_CUST_SERVICE_HEADER ON SOP_DAMAGE_CODES.ID = SOP_CUST_SERVICE_HEADER.DAMAGE_CODE RIGHT OUTER JOIN
SOP_CUSTOMER INNER JOIN
PROD_ASSEMBLY_BARCODES ON SOP_CUSTOMER.ACCOUNT = PROD_ASSEMBLY_BARCODES.ACCOUNT LEFT OUTER JOIN
PROD_PROD_BARCODES INNER JOIN
STOCK_ITEMS ON PROD_PROD_BARCODES.MATERIAL_ID = STOCK_ITEMS.MATERIAL_ID ON
PROD_ASSEMBLY_BARCODES.BARCODE = PROD_PROD_BARCODES.RECEIPT_BARCODE LEFT OUTER JOIN
SOP_RESCHEDULES ON PROD_ASSEMBLY_BARCODES.ORDER_NO = SOP_RESCHEDULES.ORDER_NO AND
PROD_ASSEMBLY_BARCODES.PRODUCTION_ID = SOP_RESCHEDULES.PLAN_ID AND
PROD_ASSEMBLY_BARCODES.LOAD_NO = SOP_RESCHEDULES.LOAD_NO ON
PROD_ASSEMBLY_BARCODES.ORDER_NO = SOP_CUST_SERVICE_HEADER.ORIGINAL_ORDER_NO
WHERE (STOCK_ITEMS.STK_PRODUCTION_LINE = 'A') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'B') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'C') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'D') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'E') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'G') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'I')
GROUP BY PROD_PROD_BARCODES.REASON_CODE, PROD_ASSEMBLY_BARCODES.SCAN_DESPATCHED_DATE,
PROD_ASSEMBLY_BARCODES.SCAN_RETURNED_DATE, PROD_PROD_BARCODES.OUTCOME, PROD_ASSEMBLY_BARCODES.MATERIAL_ID,
PROD_ASSEMBLY_BARCODES.DETAILS, PROD_ASSEMBLY_BARCODES.ACCOUNT, PROD_ASSEMBLY_BARCODES.NAME, SOP_RESCHEDULES.ID,
SOP_RESCHEDULES.ID_DESCRIPTION, PROD_ASSEMBLY_BARCODES.ORDER_NO, SOP_CUST_SERVICE_HEADER.RESPONSIBILITY,
PROD_ASSEMBLY_BARCODES.LOAD_NO, PROD_PROD_BARCODES.CREATION_DATE, STOCK_ITEMS.STK_FAMILY, SOP_CUSTOMER.INV_TO,
SOP_DAMAGE_CODES.DESCRIPTION
HAVING (PROD_ASSEMBLY_BARCODES.ORDER_NO = 2643040)
/* nested version were I thought it would summarise from prod assembly barcodes and only return 4 rows can't understand why 8!!! */
/* the subquery that contains the 4 returned pieces is called RETURNED_BARCODES. */
SELECT
COUNT(PROD_PROD_BARCODES.MATERIAL_ID) AS CountOfMATERIAL_ID,
PROD_PROD_BARCODES.REASON_CODE,
RETURNED_BARCODES.SCAN_DESPATCHED_DATE,
RETURNED_BARCODES.SCAN_RETURNED_DATE,
PROD_PROD_BARCODES.OUTCOME,
RETURNED_BARCODES.MATERIAL_ID,
RETURNED_BARCODES.DETAILS,
RETURNED_BARCODES.ACCOUNT,
RETURNED_BARCODES.NAME,
SOP_RESCHEDULES.ID,
SOP_RESCHEDULES.ID_DESCRIPTION,
RETURNED_BARCODES.ORDER_NO,
SOP_CUST_SERVICE_HEADER.RESPONSIBILITY,
RETURNED_BARCODES.LOAD_NO,
PROD_PROD_BARCODES.OUTCOME AS Expr1,
PROD_PROD_BARCODES.CREATION_DATE,
STOCK_ITEMS.STK_FAMILY,
SOP_CUSTOMER.INV_TO,
SOP_DAMAGE_CODES.DESCRIPTION AS DAMAGE_CODE
FROM
SOP_DAMAGE_CODES INNER JOIN
SOP_CUST_SERVICE_HEADER ON SOP_DAMAGE_CODES.ID = SOP_CUST_SERVICE_HEADER.DAMAGE_CODE
RIGHT OUTER JOIN PROD_PROD_BARCODES
INNER JOIN
STOCK_ITEMS ON PROD_PROD_BARCODES.MATERIAL_ID = STOCK_ITEMS.MATERIAL_ID
INNER JOIN
(
SELECT SCAN_DESPATCHED_DATE, SCAN_RETURNED_DATE, MATERIAL_ID, DETAILS, ACCOUNT, NAME, ORDER_NO, LOAD_NO, PRODUCTION_ID, BARCODE
FROM PROD_ASSEMBLY_BARCODES
WHERE (SCAN_RETURNED = 1)
GROUP BY SCAN_DESPATCHED_DATE, SCAN_RETURNED_DATE, MATERIAL_ID, DETAILS, ACCOUNT, NAME, ORDER_NO, LOAD_NO, PRODUCTION_ID, BARCODE
HAVING (ORDER_NO = 2643040)
)
as RETURNED_BARCODES
ON PROD_PROD_BARCODES.RECEIPT_BARCODE = RETURNED_BARCODES.BARCODE
INNER JOIN
SOP_CUSTOMER ON RETURNED_BARCODES.ACCOUNT = SOP_CUSTOMER.ACCOUNT
LEFT OUTER JOIN
SOP_RESCHEDULES ON RETURNED_BARCODES.ORDER_NO = SOP_RESCHEDULES.ORDER_NO
AND
RETURNED_BARCODES.PRODUCTION_ID = SOP_RESCHEDULES.PLAN_ID
AND
RETURNED_BARCODES.LOAD_NO = SOP_RESCHEDULES.LOAD_NO
ON
RETURNED_BARCODES.ORDER_NO = SOP_CUST_SERVICE_HEADER.ORIGINAL_ORDER_NO
WHERE
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'A') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'B') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'C') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'D') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'E') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'G') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'I')
GROUP BY
PROD_PROD_BARCODES.REASON_CODE,
RETURNED_BARCODES.SCAN_DESPATCHED_DATE,
RETURNED_BARCODES.SCAN_RETURNED_DATE,
PROD_PROD_BARCODES.OUTCOME,
RETURNED_BARCODES.MATERIAL_ID,
RETURNED_BARCODES.DETAILS,
RETURNED_BARCODES.ACCOUNT,
RETURNED_BARCODES.NAME,
SOP_RESCHEDULES.ID,
SOP_RESCHEDULES.ID_DESCRIPTION,
RETURNED_BARCODES.ORDER_NO,
SOP_CUST_SERVICE_HEADER.RESPONSIBILITY,
RETURNED_BARCODES.LOAD_NO,
PROD_PROD_BARCODES.CREATION_DATE,
STOCK_ITEMS.STK_FAMILY,
SOP_CUSTOMER.INV_TO,
SOP_DAMAGE_CODES.DESCRIPTION
first section of your query id like to address is this
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'A') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'B') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'C') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'D') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'E') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'G') OR
(STOCK_ITEMS.STK_PRODUCTION_LINE = 'I')
it can be rewritten as
STOCK_ITEMS.STK_PRODUCTION_LINE IN ('A','B','C','D','E','G','I')
Its semantics and both do the same thing to me the above looks better.
i think the problem is coming from the fact that you need to move (ORDER_NO = 2643040) from the HAVING to the WHERE. HAVING is used for aggregates (HAVING COUNT(*) > 2) where limiting the order number is better done in the WHERE.
In your sub query you dont need the GROUP BY or the HAVING because you have no aggergate functions (SUM, MAX, COUNT).
have you tried moving (SCAN_RETURNED = 1) and (ORDER_NO = 2643040) from your sub query to the where clause of your main query?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 6:08 am
the books online articles you may want to look at.
WHERE http://msdn.microsoft.com/en-us/library/ms188047.aspx
HAVING http://msdn.microsoft.com/en-US/library/ms180199(v=sql.90).aspx
IN http://msdn.microsoft.com/en-US/library/ms177682(v=sql.90).aspx
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 6:15 am
Thanks, the in clause looks much better I had removed all the brackets from the where but the gui outs them back in.
Looking at it I have removed the group by from the subquery as thinking about it the date field and material id means that 8 or the correct 4 will be returned with or without group by.
when I query the sub query on it's own it returns 4 rows as I bring in sop_cust_header thats when it doubles up. An order may have many pieces associated to it.
I always thought the sub query was processed first and then the other parts executed.
Thanks for your help.
April 18, 2012 at 6:51 am
I think that the table that is causing the doubling is the SOP_CUST_SERVICE_HEADER. Due to there being 2 damage codes then each damage code is matching to each of the 4 rows ie 8 rows. Assuming that a damage row item is returned for a single reason that the design would need to change to show the damage code on a per item basis not a header basis.
Fitz
April 18, 2012 at 6:53 am
its because order number in SOP_CUST_SERVICE_HEADER is not unique. you end up creating a cartesian product with the item marked returned in PROD_ASSEMBLY_BARCODES showing up as error x 27 and x 6. you need to change your table structure in SOP_CUST_SERVICE_HEADER so it includes the item number that was returned. that will solve your problem
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 7:02 am
Thanks for you help and time, yes I see that now!!! I had my query wrong, guess we could have done better on that product, to avoid 2 customer service queries!!
thanks
again
April 18, 2012 at 7:40 am
jasemilly (4/18/2012)
Thanks for you help and time, yes I see that now!!! I had my query wrong, guess we could have done better on that product, to avoid 2 customer service queries!!thanks
again
your query was returning just fine with the data it was given. also you do not need the sub query if you add SOP_CUST_SERVICE_HEADER.SCAN_RETURNED = 1 to the where clause. the only problem with your structure was not including item into SOP_CUST_SERVICE_HEADER. unless you have a different table that has the structure (OrderNumber, ItemNumber, ReturnCode) all ready.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply