Solved

SQL Statement not seeing null values

Posted on 2013-11-13
3
298 Views
Last Modified: 2013-11-13
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
Comment
Question by:Jeremy Kirkbride
3 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39646000
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
 
LVL 11

Accepted Solution

by:
David Kroll earned 500 total points
ID: 39646039
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
 

Author Closing Comment

by:Jeremy Kirkbride
ID: 39646348
Thanks so much, exactly what I need.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now