Home » SQL & PL/SQL » SQL & PL/SQL » Help needed
Help needed [message #194841] Mon, 25 September 2006 12:29 Go to next message
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 Go to previous message
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
Previous Topic: JOIN Style Option
Next Topic: Oracle Mail: Problem in UTF8 Conversion
Goto Forum:
  


Current Time: Mon Dec 05 09:06:36 CST 2016

Total time taken to generate the page: 0.10996 seconds