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
CodyPorterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
knightEknightCommented:
another alternative:

  and CUST_ORDER_LINE.USER_4 not in ('Min-Max','Min-Max VOI')
0
CodyPorterAuthor Commented:
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 ; )
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>LEFT(CUST_ORDER_LINE.USER_4,7) <> 'Min-Max'
If there are NULLS in the USER_4 column, then try this..

LEFT(COALESCE(CUST_ORDER_LINE.USER_4,'') ,7) <> 'Min-Max'

There also may be some trailing spaces to deal with..

LEFT(LTRIM(RTRIM(COALESCE(CUST_ORDER_LINE.USER_4,''))) ,7) <> 'Min-Max'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodyPorterAuthor Commented:
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!!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.