SELECT DISTINCT is omitting NULL values when not desired

Question

I am trying to build a distinct string in a query, which works unless one of the values is NULL. I've tested removing LOCATION_ADDR_LINE_2, and the query will work just fine. When I do not SELECT DISTINCT, I find that LOCATION_ADDR_LINE_2 values are NULL. How can I gather these values in the SELECT DISTINCT even if NULL?

SELECT      DISTINCT(LOCATION_ADDR_LINE_1 + ', ' + LOCATION_ADDR_LINE_2 + ', ' + LOCATION_CITY + ', ' + LOCATION_WORK_STATE) AS Addresses
FROM        OracleReport
WHERE       (LOCATION_ADDR_LINE_1 LIKE '%1135 Auto%' OR LOCATION_ADDR_LINE_1 LIKE '%199 Easy%')

Returns:

Addresses NULL

SELECT      DISTINCT(LOCATION_ADDR_LINE_1 + ', ' + LOCATION_CITY + ', ' + LOCATION_WORK_STATE) AS Addresses
FROM        [OperationReport].[dbo].[OracleReport]
WHERE       (LOCATION_ADDR_LINE_1 LIKE '%1135 Auto%' OR LOCATION_ADDR_LINE_1 LIKE '%199 Easy%')

Returns:

Addresses

1135 Auto...

189-199 Easy...

wrap each value in coalesce(field,'') to change the null to a zero length string. However if this is Oracle that may not work. Judging by + use instead of || though coalesce should work fine. — xQbert, Nov 29 '16 at 19:08
This is a SQL query against a SQL table built from Oracle data. Long story, it's part of a bigger machine that needs to not query Oracle directly. — Lord Helmet, Nov 29 '16 at 19:12
Which DBMS are you using? also: there is no point in using parentheses with distinct. It's **NOT** a function `distinct (a)` is exactly the same thing as `distinct a` — a_horse_with_no_name, Nov 29 '16 at 19:24
I know it's not a function, but I was applying the distributive property to build one distinct string. — Lord Helmet, Nov 29 '16 at 19:54
I see your point, but it's a matter of syntax preference, really. — Lord Helmet, Nov 29 '16 at 20:03

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

Assuming you don't mind text,,text,... (empty string) when a value is NULL...

SELECT  DISTINCT(coalesce(LOCATION_ADDR_LINE_1,'') + ', ' +              
                 coalesce(LOCATION_ADDR_LINE_2,'') + ', ' + 
                 coalesce(LOCATION_CITY,'')  + ', ' + 
                 coalesce(LOCATION_WORK_STATE,'')) AS Addresses
FROM  OracleReport
WHERE (LOCATION_ADDR_LINE_1 LIKE '%1135 Auto%' 
    OR LOCATION_ADDR_LINE_1 LIKE '%199 Easy%')

Coalesce will take the first non-null value and return it. It requires consistent data types and will early exit once the first non-null value in a series is encountered. (more details Oracle Differences between NVL and Coalesce)

edited May 23 '17 at 12:01

Community

1
1

answered Nov 29 '16 at 19:11

xQbert

33,725
2
39
60

Never used that before, but it worked! Thanks a million! – Lord Helmet Nov 29 '16 at 19:13
Technically that was Q*Bert – xQbert Nov 30 '16 at 19:06

SELECT DISTINCT is omitting NULL values when not desired

1 Answers1