Home » SQL & PL/SQL » SQL & PL/SQL » Creating a view contains...
Creating a view contains... [message #190423] Wed, 30 August 2006 11:32 Go to next message
Messages: 9
Registered: August 2006
Junior Member
I have a view VIEW1 created based on 16 columns, say P,A1,A2,A3,......A15.
Most of the time ,out of A1,A2,..A15, only one or two fields may contains data.

How can i create a 17th column in the view such that the value will be 'Y' if any of the columns A1,A2,...A15 contains data else it will be 'N' if all the fields(A1,A2,..A15)are null.

Can someone suggest me a method,which gives best performance?

Thanks in advance,
Re: Creating a view contains... [message #190428 is a reply to message #190423] Wed, 30 August 2006 12:23 Go to previous messageGo to next message
Messages: 6
Registered: March 2005
Junior Member
I am not sure if this would be the most efficient way but here is an option.

create or replace view VIEW1 (P,A1,A2,...,An,An+1)
select column1, column2, ...,columnN, decode(column1,null,decode(column2,null,...(decode(columnN,'n','y'),'y')))..)
from test;

Here is an example. I have a table called test that has three columns. I am selecting two columns from it for the view along with an additional column that can be used to indicate whether or not there is data in either of the other two columns. Here is the code for the view

create or replace view test_v (vc1,vc2,vc3)
select column1, column2, decode(column1,null,decode(column2,null,'n','y'),'y')
from test;

SQL> select * from test;

column1 column2 column3
---------- ---------- -------------
1 2
1 3
2 3
2 4
3 5

SQL> select * from test_v;

vc1 vC2 C
---------- ---------- -
1 2 y
1 3 y
2 3 y
2 4 y
3 5 y
Re: Creating a view contains... [message #190491 is a reply to message #190428] Thu, 31 August 2006 00:57 Go to previous messageGo to next message
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

or ,you can try something on the similar line (untested code)

select p,a1,a2,a3...a15,decode(result,null,'N','Y')status
from (
select p,a1,a2,a3..a15,coalesce(a1,a2,a3..a15)result
from <your_table_name>

Re: Creating a view contains... [message #190502 is a reply to message #190491] Thu, 31 August 2006 02:06 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or to remove a level of select nesting, you could do
select p,a1,a2,a3...a15,decode(coalesce(a1,a2,a3..a15),null,'N','Y')status
from <your_table_name>
Previous Topic: Discarding state of UTL_FILE
Next Topic: Query on 'AFTER INSERT ON '
Goto Forum:

Current Time: Wed Mar 29 10:16:24 CDT 2017

Total time taken to generate the page: 0.10862 seconds