?
Solved

SQL Statement not seeing null values

Posted on 2013-11-13
3
Medium Priority
?
303 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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

762 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