Ask the Expert

Selecting non-NULL values

How can I select non-null value columns from a table? For example, I have a table pbearn with columns empno, ename, hra, cca, da, etc. There may be null values for any column except empno (primary key). I want to select only columns which have values and I want to have the column names also for display.

    Requires Free Membership to View

I assume your table looks something like this:

     EMPNO ENAME      HRA              CCA DA
---------- ---------- --------- ---------- ---------
      7369 SMITH                      7902
      7499 ALLEN      SALESMAN
You might want to write a script that shows the non-NULL values for a given column.
--  Show_Column.sql -- display non-null values from pbearn for
--   a given column.  The primary key value is also shown

--  column_name is padded to 30 bytes to make sure that
--  columns will line up if results are merged.

SELECT    empno,
          RPAD ( UPPER ('&1'),
               )    AS column_name,
FROM      pbearn
You can run this script from SQL*Plus for each column:

@show_column  ename

     EMPNO COLUMN_NAME                    ENAME
---------- ------------------------------ ----------
      7499 ENAME                          ALLEN
      7369 ENAME                          SMITH

@show_column  hra

     EMPNO COLUMN_NAME                    HRA
---------- ------------------------------ --------
      7499 HRA                            SALESMAN
If what you really want is common output, with all values for the same empno together, like this:
---------- --------------- ----------------------------------------
      7369 CCA             7902
      7369 ENAME           SMITH
      7499 ENAME           ALLEN
      7499 HRA             SALESMAN
you could run the script for each column. Since the separate result sets all have the similar columns, you could SPOOL the results to a common file, then use a sort utility. But what am I saying? "Separate result sets", "similar columns", "common sort": these are all features of the UNION operator.

Click for part 2 of Frank's response.

This was first published in September 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: