473,288 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Need to dynamically generate a SQL SELECT which excludes NULL columns

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
Jul 19 '05 #1
4 15640
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?

Jul 19 '05 #2

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
Jul 19 '05 #3
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.

Jul 19 '05 #4

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
Jul 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: muesliflakes | last post by:
I'm trying to generate a normalized XML document out of SQL server that reflects the data structure of a table. Eg. This is what I would like to get <table name='MtFeedback'> <field...
7
by: nicholas | last post by:
Hello, Got a kind of e-commerce site. There are products with product options, such as color, size, etc All are defined a table: tbl_products: the table with the products. tbl_options: the...
4
by: serge calderara | last post by:
Dear all, I need to build a web application which collects data from an SQL server database. SQL server database tables fields can be dynamically created or extended depending on my customer...
0
by: ward | last post by:
Greetings. Ok, I admit it, I bit off a bit more than I can chew. I need to complete this "Generate Report" page for my employer and I'm a little over my head. I could use some additional...
4
by: sydney.luu | last post by:
Hello, I would greatly appreciate if someone can show me how to dynamically build a Repeater with unknown number of columns at design time. I have looked various threads in this newsgroup,...
6
by: Twobridge | last post by:
I hope someone can help me out with my problem. I have found a sql statement that basically pulls all bills filed within a certain time period and the payments made on those bills with in the...
4
by: Rob Meade | last post by:
Hi all, I played with my first bit of AJAX the other week and was pleasantly surprised that I achieved my goal..now I'd like to try something else.. Question... If I have an updatePanel,...
1
by: GayatriSharma1 | last post by:
I m having some tables in the database...depending upon the columns present in each table the grid should generate rows dynamically to display that columns in grid...how to generate columns...
4
by: Tom Urbanowicz | last post by:
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...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.