Why is my query not working?
Posted on 2007-08-10
Hi, this query was working fine up till today... and now its producing strange results when the data is the same.
Here is query:
MAX(A.MARKEDDATE) as MARKEDDATE,
MAX(A.SHIP_DATE) AS SHIP_DATE,
SUM(A.QTY_BACKORDER) AS QTY_BACKORDER,
SUM(A.QTY_REQUESTED) AS QTY_REQUESTED,
SUM(A.QTY_SHIPPED) AS QTY_SHIPPED,
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
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,
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.