Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

SQL Statement not seeing null values

My statement below gives me what I need EXCEPT the user_def_fld_2  has both null and text values, I need to see both, but currently only see results with text values.

I am new to SQL

SELECT HDR.ord_no AS 'Order#',HDR.slspsn_no AS 'Slsmn#',ord_dt AS 'Order Date',HDR.cus_no AS 'Acct#',HDR.ship_instruction_1 AS 'Ship Note',bill_to_name AS 'Bill To',Ship_to_name AS 'Ship To',hdr.user_def_fld_2 AS 'Header Note',LIN.item_no AS 'Part#',LIN.item_desc_1 AS 'Description',LIN.qty_ordered AS 'Ordered',LOC.qty_on_hand AS 'On Hand',(LOC.qty_on_hand-LIN.qty_ordered)AS 'Balance'
FROM oeordhdr_sql HDR
INNER JOIN oeordlin_sql LIN
ON HDR.ord_no=LIN.ord_no
LEFT OUTER JOIN iminvloc_sql LOC
ON LIN.item_no=LOC.item_no
WHERE HDR.user_def_fld_2 NOT LIKE 'SHOW%' AND HDR.bill_to_name not like 'REGO%' AND HDR.bill_to_name not like 'RFTC%' AND HDR.user_def_fld_2 NOT LIKE '30 DA%' AND HDR.ord_type='O'
ORDER BY HDR.ord_dt ASC
0
Jeremy Kirkbride
Asked:
Jeremy Kirkbride
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
You can't compare NULL to anything, even a LIKE comparison, so to get around one way is to convert all NULL values to somethign else (replace 'banana' with whatever you want), then the LIKE comaprison will work against that.

WHERE COALESCE(HDR.user_def_fld_2, 'banana') NOT LIKE 'SHOW%'
   AND ...
0
 
David KrollCommented:
SELECT HDR.ord_no AS 'Order#',HDR.slspsn_no AS 'Slsmn#',ord_dt AS 'Order Date',HDR.cus_no AS 'Acct#',HDR.ship_instruction_1 AS 'Ship Note',bill_to_name AS 'Bill To',Ship_to_name AS 'Ship To',hdr.user_def_fld_2 AS 'Header Note',LIN.item_no AS 'Part#',LIN.item_desc_1 AS 'Description',LIN.qty_ordered AS 'Ordered',LOC.qty_on_hand AS 'On Hand',(LOC.qty_on_hand-LIN.qty_ordered)AS 'Balance'
FROM oeordhdr_sql HDR
INNER JOIN oeordlin_sql LIN
ON HDR.ord_no=LIN.ord_no
LEFT OUTER JOIN iminvloc_sql LOC
ON LIN.item_no=LOC.item_no
WHERE  HDR.bill_to_name not like 'REGO%' AND HDR.bill_to_name not like 'RFTC%' AND  AND HDR.ord_type='O' AND
(HDR.user_def_fld_2 IS NULL OR (HDR.user_def_fld_2 NOT LIKE 'SHOW%' AND HDR.user_def_fld_2 NOT LIKE '30 DA%'))
ORDER BY HDR.ord_dt ASC
0
 
Jeremy KirkbrideAuthor Commented:
Thanks so much, exactly what I need.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now