OraFAQ Forum: SQL & PL/SQL » View with column select

Home » SQL & PL/SQL » SQL & PL/SQL » View with column select

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

View with column select [message #1399]

Fri, 26 April 2002 05:28

David Jones
Messages: 4
Registered: April 2002

Junior Member

We have a control table with about 200 columns. I would like to create a view of this table that would just select the columns that are not null(5-15 columns). Does anyone know of a way to do this all in a view?

Report message to a moderator

Re: View with column select [message #1403 is a reply to message #1399]

Fri, 26 April 2002 07:09

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

are u lookin into something like this?

------------
source table
------------
SQL> desc kk
Name Null? Type
-------------
A NOT NULL NUMBER
B NOT NULL NUMBER
c NUMBER
D NOT NULL NUMBER
e NUMBER
---------
code
--------------
declare
cursor c1 is select column_name from user_tab_cols where table_name='KK'
and nullable='N';
str varchar(300);
cname varchar2(300);
a varchar2(30);
begin
for mag in c1 loop
exit when c1%notfound;
cname:=cname||','||mag.column_name;
cname:=ltrim(cname,',');
end loop;
str:='create view view1 as select ' || cname||' from KK';
execute immediate str;
end;
/

SQL> desc view1
Name Null? Type
----------------------------------------------------- -------- --------
A NOT NULL NUMBER
B NOT NULL NUMBER
D NOT NULL NUMBER

Report message to a moderator

Re: View with column select [message #1406 is a reply to message #1399]

Fri, 26 April 2002 08:19

David Jones
Messages: 4
Registered: April 2002

Junior Member

Very close, This does help for future coding.

I was looking at the actual data in the table to see if it was null or blank or '', and only show the columns that were not null in the view,
not if the column could be null or not. Thanks

Report message to a moderator

Previous Topic:	Trigger To Drop Table
Next Topic:	max

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:00:07 CDT 2024