Re: Need to dynamically generate a SQL SELECT which excludes NULL columns

Originally posted by Tom Urbanowicz
> To clarify, if only 3 columns in a record are populated (in a table w/
> 100 columns), I don't want a query to retrieve 97 NULLs with 97 column
> headings. Instead, I need SQL (or PL/SQL) that:
> A) Identifies which columns in the table are populated
> for a specific record.
> B) Creates a SELECT statement using only those populated
> columns in the list of columns.
>
> I would be able to identify the columns in SQL*Plus, because I would
> have the headings on. For example, the generated SQL and the result
> set would be:
>
> set heading on
> SQL> select COL1, COL8, COL93
> 2 from MYTEST
> 3 where COL1='35';
>
> COL1 COL8 COL93
> ---------- ---------- ---------
> 35 44 21-JUL-03
>
>
> Thanks for your insights.
>
> aruneeshsalhotr wrote in message news:news:...
> > It certainly is possibly what you are trying to do.
> > But how would you know what columns the result is refering
  to.
> > I mean if the query retruns col1, col2 and col93, how would u
  know what
> > columns are we talking about.
> >
> > Though I dont understand the purpose of this query, but I could
  give
> > it a shot.

You want to do this for a SINGLE record, not a set of records?

If so then you can use DBMS_SQL to query the record and return each column in turn. If it is NULL, skip it, otherwise output the column name and value.

This procedure (based loosely on Tom Kyte's print_table procedure) will do that:

create or replace procedure no_nulls( p_sql in varchar2 ) is

  v_sql     varchar2(32767) := p_sql;
  v_cursor  integer := dbms_sql.open_cursor;
  v_value   varchar2(4000);
  v_status  integer;
  v_desctab dbms_sql.desc_tab;
  v_numcols integer;
  v_header1 varchar2(4000);
  v_header2 varchar2(4000);
  v_record  varchar2(4000);
  v_length  integer;

    dbms_sql.parse( v_cursor, v_sql, dbms_sql.native );     dbms_sql.describe_columns( v_cursor, v_numcols, v_desctab );

    for i in 1 .. v_numcols loop

        dbms_sql.define_column(v_cursor, i, v_value, 4000);     end loop;

    v_status := dbms_sql.execute(v_cursor);

    while ( dbms_sql.fetch_rows(v_cursor) > 0 ) loop

        v_header1 := '';
        v_header2 := '';
        v_record  := '';
        for i in 1 .. v_numcols loop
            dbms_sql.column_value( v_cursor, i, v_value );
            IF v_value IS NOT NULL THEN
              IF v_desctab(i).col_type = 1 THEN -- Varchar2
                v_length := v_desctab(i).col_max_len;
              ELSIF v_desctab(i).col_type = 12 THEN -- Date
                v_length := 11;
              ELSE -- Assumes number!
                v_length := v_desctab(i).col_precision+2;
              END IF;
              v_header1 := v_header1 || RPAD( v_desctab(i).col_name, v_length ) || ' ';
              v_header2 := v_header2 || RPAD( '-', v_length, '-' ) || ' ';
              v_record  := v_record  || RPAD( v_value, v_length ) || ' ';
            END IF;
        end loop;
        dbms_output.put_line( v_header1 );
        dbms_output.put_line( v_header2 );
        dbms_output.put_line( v_record );

For example:

SQL> exec no_nulls('select * from emp where ename=''KING''') EMPNO ENAME JOB HIREDATE SAL DEPT ------ ---------- --------- ----------- --------- ---- 7839 KING PRESIDENT 17-NOV-1981 5000 10 PL/SQL procedure successfully completed.

The above code only handles VARCHAR2, DATE and NUMBER.

--
Posted via http://dbforums.com