?
Solved

Euqivalent for Oracle of If function

Posted on 2006-10-19
9
Medium Priority
?
2,299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

801 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