Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Euqivalent for Oracle of If function

Posted on 2006-10-19
9
Medium Priority
?
2,325 Views
Last Modified: 2008-02-20
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.
0
Comment
Question by:looper8
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17766351
select case when cost < 500 then 0 else cost - 500 end from dual;

Thanks
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 17766355
Use SELECT CASE WHEN ... THEN
http://www.psoug.org/reference/decode_case.html
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17766359
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:looper8
ID: 17766533
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
 
LVL 6

Accepted Solution

by:
Ritesh_Garg earned 1500 total points
ID: 17766541
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
 
LVL 1

Author Comment

by:looper8
ID: 17766579
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
 
LVL 1

Author Comment

by:looper8
ID: 17766612
Ah ... this works:

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

Thanks
0
 
LVL 8

Expert Comment

by:Pierrick LOUBIER
ID: 17769838
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 17772104
looper8,

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

Thanks
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question