Q

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.

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'),
                 30
               )    AS column_name,
          &1
FROM      pbearn
WHERE     &1  IS NOT NULL
ORDER BY  &1;
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:
     EMPNO COLUMN_NAME     VALUE
---------- --------------- ----------------------------------------
      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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close