Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Statement not seeing null values

Posted on 2013-11-13
3
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 66

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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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