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.
LVL 1
looper8Asked:
Who is Participating?
 
Ritesh_GargConnect With a Mentor Commented:
Case would be the best.  However if you have prior to 9i, you may use decode like:

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

The sign fn will return 1 if cost > 500 and thus cost - 500 will be returned.
The sign fn will will give 0 or -1 if cost <= 500 and so the default value of 0 will be returned.

Thanks,
Ritesh
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
select case when cost < 500 then 0 else cost - 500 end from dual;

Thanks
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
Use SELECT CASE WHEN ... THEN
http://www.psoug.org/reference/decode_case.html
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
looper8Author Commented:
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?
0
 
looper8Author Commented:
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
0
 
looper8Author Commented:
Ah ... this works:

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

Thanks
0
 
Pierrick LOUBIERIS Operational Excellence ManagerCommented:
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
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
looper8,

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

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.