Need to dynamically generate a SQL SELECT which excludes NULL columns

[Tom Urbanowicz]

I have a table with 100+ columns, for which I'm trying to retrieve
only 1 specific record. For this single record, I do not know which of
the columns are NULL, and which are populated.

I would like to create a dynamically-generated SELECT--limiting the
columns to only those that are populated. If, for example, only
columns COL1, COL8, and COL93 are populated for this one record in the
MYTEST table, the generated SELECT statement would be:
select COL1, COL8, COL93
from MYTEST
where COL1='current_value';

as opposed to:
select COL1, COL2, COL3, COL4, COL5, COL6 . . .
from MYTEST
where COL1='current_value';

In PL/SQL, I've been wrangling to do this--obtaining the list of
columns from USER_TAB_COLUMNS for the table, using a temporary table
to store both the column's value and the column's name for further
analysis, etc. However, this is seemingly cumbersome. Isn't there an
easier way to efficiently identify which columns are populated or not
for a specific row in a table?

Thanks in advance.
-Tom

[Anna C. Dent]

Tom Urbanowicz wrote:

I have a table with 100+ columns, for which I'm trying to retrieve
only 1 specific record. For this single record, I do not know which of
the columns are NULL, and which are populated.

I would like to create a dynamically-generated SELECT--limiting the
columns to only those that are populated. If, for example, only
columns COL1, COL8, and COL93 are populated for this one record in the
MYTEST table, the generated SELECT statement would be:
select COL1, COL8, COL93
from MYTEST
where COL1='current_value';

as opposed to:
select COL1, COL2, COL3, COL4, COL5, COL6 . . .
from MYTEST
where COL1='current_value';

In PL/SQL, I've been wrangling to do this--obtaining the list of
columns from USER_TAB_COLUMNS for the table, using a temporary table
to store both the column's value and the column's name for further
analysis, etc. However, this is seemingly cumbersome. Isn't there an
easier way to efficiently identify which columns are populated or not
for a specific row in a table?

Thanks in advance.
-Tom

It depends.

What problem are you REALLY trying to solve?

What is wrong with getting the whole row; including the nulls?

[aruneeshsalhotr]

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.

--
Posted via http://dbforums.com

[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 <me*********@dbforums.com> wrote in message news:<30****************@dbforums.com>...

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.

[andrewst]

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;
begin

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 );
end loop;
end;
/

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