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.