• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Why is my query not working?

Hi, this query was working fine up till today... and now its producing strange results when the data is the same.  

Here is query:

SELECT
MAX(A.MARKEDDATE) as MARKEDDATE,
A.DEMAND_SOURCE,
A.HOLD_CD,
A.HOLD_FLAG,
A.CANCEL_FLAG,
A.BUSINESS_UNIT,
MAX(A.SHIP_DATE) AS SHIP_DATE,
A.ORDER_NO,
A.ORDER_INT_LINE_NO,
B.CUSTOMERNUMBER,
B.CUSTOMERCLASS1,
B.CUSTOMERCLASS2,
B.CUSTOMERCLASS3,
B.CUSTOMERCLASS4,
B.DROPSHIP,
B.SUPPORTTEAM,
B.CLEANDEPTCODE,
SUM(A.QTY_BACKORDER) AS QTY_BACKORDER,
SUM(A.QTY_REQUESTED) AS QTY_REQUESTED,
SUM(A.QTY_SHIPPED) AS QTY_SHIPPED,
A.INVOICE,
A.INV_ITEM_ID,
B.UNITPRICE,
B.UNITCOST
INTO TEMPSHIP
FROM SHIP_INF_INV A LEFT JOIN PRICINGDATA B ON A.ORDER_NO = B.ORDERNUMBER AND A.ORDER_INT_LINE_NO = B.LINENUMBER2
AND A.INVOICE = B.INVOICENUMBER AND A.INV_ITEM_ID = B.ITEMNUMBER
GROUP BY
A.DEMAND_SOURCE, A.HOLD_CD, A.HOLD_FLAG, A.CANCEL_FLAG,
A.BUSINESS_UNIT, A.ORDER_NO, A.ORDER_INT_LINE_NO, B.CUSTOMERCLASS1,
B.CUSTOMERCLASS2,B.CUSTOMERCLASS3,B.CUSTOMERCLASS4, B.CUSTOMERNUMBER, B.DROPSHIP, B.SUPPORTTEAM,
B.CLEANDEPTCODE,A.INVOICE, A.INV_ITEM_ID,B.UNITPRICE,
B.UNITCOST
HAVING MAX(SHIP_DATE) >= '1/1/2006'

The main table its pulling from (SHIP_INF_INV) has 483226 records in it.  
The query only returns 103k records even though its a left join.  Why isn't it returning the right number of records when its a left join?

Also, my having clause does not work at all.

Based on the join its doing...there should be a one to one relationship between the main table and the joining table.  I can't figure out the logic in which it returns only 100k records.  This is in a big stored procedure and the procedure was working fine till today.

Any help is appreciated.
0
Roxanne25
Asked:
Roxanne25
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Also, my having clause does not work at all.
you might consider this small change:

HAVING MAX(SHIP_DATE) >= convert(datetimme, '1/1/2006', 101)
0
 
Roxanne25Author Commented:
k I will try that...I'm more concerned about it not returning the right number of records though.
0
 
SwindleCommented:
You are grouping, so is it possible that you have some items that you are selecting out that are exact duplicates?  You could try adding a count(*) in your SELECT clause and ORDER BY count(*) desc to see.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
lahousdenCommented:
<<Why isn't it returning the right number of records when its a left join?>> - What is the "right number" of records and why can't it be 103k?
0
 
Scott PletcherSenior DBACommented:
Since your selection is specified only against table A, and the SUMs are on table A, I think you should move the table A part into a subquery / derived table to (1) avoid corrupting/overcounting your SUMs because of the join (2) reduce the complexity and overhead of the grouping.  For example, see if this produces a different -- and hopefully more accurate :-) -- result set:


SELECT
A.MARKEDDATE,
A.DEMAND_SOURCE,
A.HOLD_CD,
A.HOLD_FLAG,
A.CANCEL_FLAG,
A.BUSINESS_UNIT,
A.SHIP_DATE,
A.ORDER_NO,
A.ORDER_INT_LINE_NO,
B.CUSTOMERNUMBER,
B.CUSTOMERCLASS1,
B.CUSTOMERCLASS2,
B.CUSTOMERCLASS3,
B.CUSTOMERCLASS4,
B.DROPSHIP,
B.SUPPORTTEAM,
B.CLEANDEPTCODE,
A.QTY_BACKORDER,
A.QTY_REQUESTED,
A.QTY_SHIPPED,
A.INVOICE,
A.INV_ITEM_ID,
B.UNITPRICE,
B.UNITCOST
INTO TEMPSHIP
FROM (
    SELECT
    MAX(MARKEDDATE) as MARKEDDATE,
    DEMAND_SOURCE,
    HOLD_CD,
    HOLD_FLAG,
    CANCEL_FLAG,
    BUSINESS_UNIT,
    MAX(SHIP_DATE) AS SHIP_DATE,
    ORDER_NO,
    ORDER_INT_LINE_NO,
    SUM(QTY_BACKORDER) AS QTY_BACKORDER,
    SUM(QTY_REQUESTED) AS QTY_REQUESTED,
    SUM(QTY_SHIPPED) AS QTY_SHIPPED,
    INVOICE,
    INV_ITEM_ID
    FROM SHIP_INF_INV
    GROUP BY
    ORDER_NO, ORDER_INT_LINE_NO,
    INVOICE, INV_ITEM_ID,
    DEMAND_SOURCE, HOLD_CD, HOLD_FLAG, CANCEL_FLAG,
    BUSINESS_UNIT
    HAVING MAX(SHIP_DATE) >= '1/1/2006'
) AS A
LEFT JOIN PRICINGDATA B ON A.ORDER_NO = B.ORDERNUMBER AND A.ORDER_INT_LINE_NO = B.LINENUMBER2
AND A.INVOICE = B.INVOICENUMBER AND A.INV_ITEM_ID = B.ITEMNUMBER
0
 
Roxanne25Author Commented:
Ah!  Ty... that does the trick :D
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now