asked on

Euqivalent for Oracle of If function

Trying to write a query in Discoverer and can't find any sort of If function. I just want to do what in Excel would be:

If([Cost] < 500, 0, [Cost] - 500)

I.e. if the Cost field is less than 500 replace the value with 0, otherwise deduct 500 from the value.

Naveen Kumar

select case when cost < 500 then 0 else cost - 500 end from dual;

Thanks

Pierrick LOUBIER

Use SELECT CASE WHEN ... THEN
http://www.psoug.org/reference/decode_case.html

Naveen Kumar

Say, if your table name is table1 and you are trying it for col1 then,

select case when col1 < 500 then 0 else col1 - 500 end col1_actual_value
from table1;

Thanks

looper8

ASKER

Thanks. Don't quite understand. Perhaps I haven't explained what I'm doing properly.

I'm using Oracle Discoverer to create a report and I'm trying to put in a Calculation field. Can I put a SELECT statement in there?

ASKER CERTIFIED SOLUTION

Ritesh_Garg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

looper8

ASKER

Yes, we're on Oracle 8 ... so I've tried this

Select Decode( sign("cost SUM" - 500), 1, "cost SUM" - 500, 0)
from dual;

(The field is actually called "cost SUM")

And I get:

Error in formula, unexpected end of formula
Error: Function SELECT DECODE has not been registered with the EUL
Error: Syntax error: token from dual at position 73

looper8

ASKER

Ah ... this works:

DECODE(SIGN(Cost SUM-500),1,Cost SUM-500,0)

Thanks

Pierrick LOUBIER

Ritesh_Garg, as far as I know, CASE was introduced with 8i. See http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1029291

Naveen Kumar

looper8,

case is there in oracle 8i itself. you can check it out.

Thanks