Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Check for positive value

RadhakrishnaSarmaFeb 27 2006 — edited Feb 27 2006

Hi,
I have to create a view based on a table whose column (NUMBER datatype)value may be negative. I have to have 0 in the view if it is negative or the value. Only with SQL, please let me know how to display 0 if column value is negative. If it is positive or zero, the actual value has to be displayed. The below will not work, but do we have any other alternative?

SQL> select * from temp_2
  2  /

C      COL_2
- ----------
A         -1

SQL> select decode(col_2<0, TRUE, 0, col_2) from temp_2
  2  /
select decode(col_2<0, TRUE, 0, col_2) from temp_2
                   *
ERROR at line 1:
ORA-00907: missing right parenthesis

SQL> ed
Wrote file afiedt.buf

  1* select case(col_2) (col_2 > 0) then 0 else col_2 from temp_2
SQL> /
select case(col_2) (col_2 > 0) then 0 else col_2 from temp_2
                          *
ERROR at line 1:
ORA-00907: missing right parenthesis

I know that TRUE or FLASE is not SQL defined constant. Please let m eknow the alternative. Forgive me if this is quite obvious.

Cheers
Sarma.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 27 2006
Added on Feb 27 2006
7 comments
1,631 views