?
Solved

Function to replace CASE

Posted on 2003-03-22
4
Medium Priority
?
454 Views
Last Modified: 2012-05-12
While converting a stored procedure from SQL Server to Oracle, I need a function to replace the following CASE statement (I am using 8i so cannot use the case in PL/SQL):

         CASE
          WHEN TrnTable.TrnDate <= CurrentValue.TillDate_
           THEN SUM(TrnTable.TrnAmount)
       ELSE 0
       END  "TrnValue"

(CurrentValue.TillDate_ is the parameter to the sp)

Can anybody point me in the right direction?

Thanks in advance...
0
Comment
Question by:Clarion
[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
  • 2
4 Comments
 
LVL 8

Expert Comment

by:Danielzt
ID: 8188216
try this:

decode(sign(TrnTable.TrnDate - CurrentValue.TillDate),-1,
SUM(TrnTable.TrnAmount),0,SUM(TrnTable.TrnAmount),0)
0
 

Author Comment

by:Clarion
ID: 8188946
Why does SUM(TrnTable.TrnAmount),0 have to be repeated twice?
0
 
LVL 6

Accepted Solution

by:
venkotch earned 200 total points
ID: 8190189
Actually it should be:

decode(sign(TrnTable.TrnDate - CurrentValue.TillDate),1,
0,SUM(TrnTable.TrnAmount))

which can be readed as
 if TrnTable.TrnDate - CurrentValue.TillDate > 0 then 0
 else SUM(TrnTable.TrnAmount)
0
 

Author Comment

by:Clarion
ID: 8193198
Thanks very much...

So, after all, I will be able to work in 8i (for some time at least).
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

770 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