0

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

Lord Helmet
  • 150
  • 14
  • 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
  • For Oracle, there's NVL() – Alexey Soshin Nov 29 '16 at 19:09
  • 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
  • 1
    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` –  Nov 29 '16 at 19:24
  • The parentheses are useless nevertheless –  Nov 29 '16 at 19:39
  • 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

1 Answers1

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)

Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62