Search code examples
sqloracle-databasesql-null

How to Select all Columns from a Table for a Record - but to exclude a Column if all entries for that Record in said Column are null?

I have a schema in a read-only Oracle database called MAXSCHEMA and a table in it called WORKORDER. This table has a column called WONUM.

If I use this SQL statement:

SELECT *
FROM MAXSCHEMA.WORKORDER
WHERE WONUM = '101319066'

I get the following 3 rows as result:

WONUM PARENT FAILDATE STATUS
101319066 null null CLOSE
101319066 101319063 null CAN
101319066 null null CLOSE

I'd like to exclude the FAILDATE column, as all entries for that WONUM record are null in that column.

In contrast, I'd like to keep the PARENT column as there is at least 1 non-null value in that column.

So my desired result would be:

WONUM PARENT STATUS
101319066 null CLOSE
101319066 101319063 CAN
101319066 null CLOSE

I understand that I can just overtly state the columns that I want:

SELECT WONUM, PARENT, STATUS
FROM MAXSCHEMA.WORKORDER

but is there any other way to exclude the FAILDATE column based on the fact that all entries for that WONUM value are null?

Solution

  • Once you have specified '*' in your query, it is going to return all the columns from the table no matter what their values are, including null values. So there is no workaround for that, other than explicitly removing the column from the query.