Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Euqivalent for Oracle of If function

Posted on 2006-10-19
9
Medium Priority
?
2,312 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

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.

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

598 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