Roxanne25
asked on
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.CUSTOME
B.CLEANDEPTCODE,A.INVOICE,
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.
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.CUSTOME
B.CLEANDEPTCODE,A.INVOICE,
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.
ASKER
k I will try that...I'm more concerned about it not returning the right number of records though.
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.
<<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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah! Ty... that does the trick :D
you might consider this small change:
HAVING MAX(SHIP_DATE) >= convert(datetimme, '1/1/2006', 101)