Help needed [message #194841] |
Mon, 25 September 2006 12:29  |
maxboom123
Messages: 9 Registered: August 2006
|
Junior Member |
|
|
Hi All,
I have a view with following columns,
P ,A1,A2,A3,A4.
Data in the view will be as follows
P A1 A2 A3 A4
== == == == ==
10 - 0 - 0
20 0 0 - 0
30 .2 0 0 -
40 - - - -
50 0 .2 1 10
[Here '-' indicates null value]
I want to add a new column to the view ,say status which has values Y/N.
The value will be Y, if atleast one of the columns(A1,A2,A3,A4) contains a value>0, else the status should be N
This is shown below.
P A1 A2 A3 A4 status
== == == == == ======
10 - 0 - 0 N
20 0 0 - 0 N
30 .2 0 0 - Y
40 - - - - N
50 0 .2 1 10 Y
How this can be implemented?
Can some one please suggest me suitable method?
Thanks in advance..
MX
|
|
|
Re: Help needed [message #194846 is a reply to message #194841] |
Mon, 25 September 2006 13:17  |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
SQL> select * from test1;
P A1 A2 A3 A4
---------- ------- ------- ------- -------
10 0 0
20 0 0 0
30 .2 0 0
40
50 0 .2 1 10
SQL> select p, a1, a2, a3, a4,
2 decode(sign(greatest(nvl(a1,0),nvl(a2,0),nvl(a3,0),nvl(a4,0))),1,'Y','N')
3 from test1;
P A1 A2 A3 A4 D
---------- ------- ------- ------- ------- -
10 0 0 N
20 0 0 0 N
30 .2 0 0 Y
40 N
50 0 .2 1 10 Y
|
|
|