Solved

Euqivalent for Oracle of If function

Posted on 2006-10-19
9
2,250 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Accepted Solution

by:
Ritesh_Garg earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now