Solved

SQL Statement not seeing null values

Posted on 2013-11-13
3
297 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks so much, exactly what I need.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 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

14 Experts available now in Live!

Get 1:1 Help Now