Oracle SQL – How to Omit Multiple Columns Where Not Null

nulloracle

I've constructed the below query which satisfies the requirements, except output includes NULL values between all "vendor_address2" columns, which isn't intended. I suspect my error is somewhere within the WHERE clause, which is designed to exclude any NULL values in the resulting output. I've included a copy of the corresponding table below the output for my most recent query:

SELECT vendor_name,
       vendor_address1 || ' ' || vendor_address2 || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
WHERE (vendor_address1 || vendor_address2 || vendor_city || vendor_state || vendor_zip_code) IS NOT NULL
ORDER BY vendor_name;

The resulting output:

Here is the corresponding table-view:

Thank you.

Best Answer

The screenshot of your data and output clearly shows the problem. The concatenation operator automatically takes care of NULL and you don't need to do anyhting. But you don't have NULL in your tables, you have 'NULL', a string, in your tables.

SQL> select 'A' || ' ' || null || ' ' || 'B' from dual;

'A'|
----
A  B

SQL> select 'A' || ' ' || 'NULL' || ' ' || 'B' from dual;

'A'||''|
--------
A NULL B

If you really had NULL in your table, SQL Developer would display (null) in the data grid.

Clean your data, then try again.

update ap.vendors set vendor_address2 = null where vendor_address2 = 'NULL';

Alternatively, you can turn string 'NULL' to a real NULL on the fly:

SELECT vendor_name,
       vendor_address1 || ' ' || decode(vendor_address2, 'NULL', null, vendor_address2) || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
ORDER BY vendor_name;

But don't do that. Take care of your data.

Related Solutions

Oracle – How to Display Number of Days Between Dates Exceeding 30 Days

Either restate the calculation in the predicate

SELECT order_id,
       (shipped_date - order_date) AS "Number of Days elapsed"
FROM orders
WHERE shipped_date - order_date > 30
ORDER BY (shipped_date - order_date) desc;

or nest the query

SELECT order_id, "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) AS "Number of Days elapsed"
          FROM orders)
 WHERE "Number of Days elapsed" > 30
 ORDER BY "Number of Days elapsed" desc

If you are going to nest the query, though, it probably makes sense to pick a case-insensitive alias in the inner query and to do the case-sensitive aliasing later on

SELECT order_id, days_elapsed "Number of Days elapsed"
  FROM (SELECT order_id,
               (shipped_date - order_date) days_elapsed
          FROM orders)
 WHERE days_elapsed > 30
 ORDER BY days_elapsed  desc

Oracle – How to Exclude Rows Containing Null Values

SELECT * FROM (
SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
) 
WHERE NewSalutation IS NOT NULL;

If that's your only section in the CASE statement, a logically equivalent query would be:

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
WHERE salutation = 'Dr.';

... as anything other than Dr. will produce a NULL NewSalutation.

Best Answer

Related Solutions

Oracle – How to Display Number of Days Between Dates Exceeding 30 Days

Oracle – How to Exclude Rows Containing Null Values

Related Question