CodyPorter
asked on
Does Not Equal Query Not Returning Correct Results
I'm having a little trouble with a query. My query works perfectly, until I add the very last "Where Clause"
I do not want 'Min-Max' or 'Min-Max VOI' to show up in User_4 field. If I add any statement to this column, I get the incorrect results.
There should be items with numbers in my results numbers and nulls. But when I run the query, these items disappear. Do I need to add INSTRING of some sort?
SELECT CUSTOMER.NAME, SHIPPER.PACKLIST_ID, SHIPPER_LINE.LINE_NO AS ShipperLine, SHIPPER.SHIPPED_DATE, CUSTOMER_ORDER.CUSTOMER_PO _REF,
PART.DESCRIPTION, PART.PRODUCT_CODE, CUST_ORDER_LINE.DESIRED_SH IP_DATE, CUST_ORDER_LINE.PROMISE_DA TE, CUST_ORDER_LINE.PART_ID,
CUST_ORDER_LINE.ORDER_QTY, DATEPART(YEAR, CUST_ORDER_LINE.DESIRED_SH IP_DATE) AS Year, DATEPART(MONTH,
CUST_ORDER_LINE.DESIRED_SH IP_DATE) AS Month, CUST_ORDER_LINE.CUSTOMER_P ART_ID, SHIPPER_LINE.SHIPPED_QTY, DATEDIFF(d,
CUST_ORDER_LINE.PROMISE_DA TE, SHIPPER.SHIPPED_DATE) AS DaysLate, CUST_ORDER_LINE.USER_4
FROM SHIPPER_LINE INNER JOIN
SHIPPER ON SHIPPER_LINE.PACKLIST_ID = SHIPPER.PACKLIST_ID INNER JOIN
CUSTOMER_ORDER INNER JOIN
CUSTOMER ON CUSTOMER_ORDER.CUSTOMER_ID = CUSTOMER.ID INNER JOIN
CUST_ORDER_LINE ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER _ID ON
SHIPPER_LINE.CUST_ORDER_ID = CUST_ORDER_LINE.CUST_ORDER _ID AND
SHIPPER_LINE.CUST_ORDER_LI NE_NO = CUST_ORDER_LINE.LINE_NO LEFT OUTER JOIN
PART ON CUST_ORDER_LINE.PART_ID = PART.ID
WHERE (CUST_ORDER_LINE.PART_ID IS NOT NULL) AND (CUST_ORDER_LINE.ORDER_QTY > 0) AND (SHIPPER_LINE.SHIPPED_QTY > 0) AND
(NOT (CUST_ORDER_LINE.USER_4 LIKE 'MIN-%'))
TIA
I do not want 'Min-Max' or 'Min-Max VOI' to show up in User_4 field. If I add any statement to this column, I get the incorrect results.
There should be items with numbers in my results numbers and nulls. But when I run the query, these items disappear. Do I need to add INSTRING of some sort?
SELECT CUSTOMER.NAME, SHIPPER.PACKLIST_ID, SHIPPER_LINE.LINE_NO AS ShipperLine, SHIPPER.SHIPPED_DATE, CUSTOMER_ORDER.CUSTOMER_PO
PART.DESCRIPTION, PART.PRODUCT_CODE, CUST_ORDER_LINE.DESIRED_SH
CUST_ORDER_LINE.ORDER_QTY,
CUST_ORDER_LINE.DESIRED_SH
CUST_ORDER_LINE.PROMISE_DA
FROM SHIPPER_LINE INNER JOIN
SHIPPER ON SHIPPER_LINE.PACKLIST_ID = SHIPPER.PACKLIST_ID INNER JOIN
CUSTOMER_ORDER INNER JOIN
CUSTOMER ON CUSTOMER_ORDER.CUSTOMER_ID
CUST_ORDER_LINE ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER
SHIPPER_LINE.CUST_ORDER_ID
SHIPPER_LINE.CUST_ORDER_LI
PART ON CUST_ORDER_LINE.PART_ID = PART.ID
WHERE (CUST_ORDER_LINE.PART_ID IS NOT NULL) AND (CUST_ORDER_LINE.ORDER_QTY
(NOT (CUST_ORDER_LINE.USER_4 LIKE 'MIN-%'))
TIA
another alternative:
and CUST_ORDER_LINE.USER_4 not in ('Min-Max','Min-Max VOI')
and CUST_ORDER_LINE.USER_4 not in ('Min-Max','Min-Max VOI')
ASKER
thank you!
WHERE ... AND LEFT(CUST_ORDER_LINE.USER_ 4,7) <> 'Min-Max' did not work, same results. . .
and CUST_ORDER_LINE.USER_4 not in ('Min-Max','Min-Max VOI') worked for the Min-Max, but my is nulls are not showing up.
I'm almost there. . .strange though that they are not showing up?
btw. . thanks for the advice on the aliases . . I'm working on that ; )
WHERE ... AND LEFT(CUST_ORDER_LINE.USER_
and CUST_ORDER_LINE.USER_4 not in ('Min-Max','Min-Max VOI') worked for the Min-Max, but my is nulls are not showing up.
I'm almost there. . .strange though that they are not showing up?
btw. . thanks for the advice on the aliases . . I'm working on that ; )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
LEFT(CUST_ORDER_LINE.USER_ 4,7) <> 'Min-Max', didn't work, BUT!!!
This worked . . .
LEFT(COALESCE(CUST_ORDER_L INE.USER_4 ,'') ,7) <> 'Min-Max'
I'm not sure why this worked, can you explain the code a bit. . . .and yes there was trailing spaces in the Min-Max. . .I fixed that piece when I found them.
Thank you!!
This worked . . .
LEFT(COALESCE(CUST_ORDER_L
I'm not sure why this worked, can you explain the code a bit. . . .and yes there was trailing spaces in the Min-Max. . .I fixed that piece when I found them.
Thank you!!
COALESCE (and it's buddy ISNULL) essentially take the first non-null value it finds, so
COALESCE(7, NULL) = 7
COALESCE(NULL, '') = empty string '', which can be damn handy in trapping nulls.
Thanks for the grade. Good luck with your project. -Jim
COALESCE(7, NULL) = 7
COALESCE(NULL, '') = empty string '', which can be damn handy in trapping nulls.
Thanks for the grade. Good luck with your project. -Jim
Open in new window
btw I HIGHLY ( HIGH .... LEE ) recommend using table aliases of two characters or less in your T-SQL, so instead of having to sift through dozens of references to CUSTOMER or SHIPPER_LINE in column names, it's just c or sl, which is much easier to read.