Link to home
Start Free TrialLog in
Avatar of CodyPorter
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_SHIP_DATE, CUST_ORDER_LINE.PROMISE_DATE, CUST_ORDER_LINE.PART_ID,
                      CUST_ORDER_LINE.ORDER_QTY, DATEPART(YEAR, CUST_ORDER_LINE.DESIRED_SHIP_DATE) AS Year, DATEPART(MONTH,
                      CUST_ORDER_LINE.DESIRED_SHIP_DATE) AS Month, CUST_ORDER_LINE.CUSTOMER_PART_ID, SHIPPER_LINE.SHIPPED_QTY, DATEDIFF(d,
                      CUST_ORDER_LINE.PROMISE_DATE, 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_LINE_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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

How about... ?
WHERE ... AND LEFT(CUST_ORDER_LINE.USER_4,7) <> 'Min-Max'

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.
another alternative:

  and CUST_ORDER_LINE.USER_4 not in ('Min-Max','Min-Max VOI')
Avatar of CodyPorter
CodyPorter

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 ; )
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
LEFT(CUST_ORDER_LINE.USER_4,7) <> 'Min-Max', didn't work, BUT!!!

This worked . . .
LEFT(COALESCE(CUST_ORDER_LINE.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!!
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