• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

Function to replace CASE

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
Clarion
Asked:
Clarion
  • 2
1 Solution
 
DanielztCommented:
try this:

decode(sign(TrnTable.TrnDate - CurrentValue.TillDate),-1,
SUM(TrnTable.TrnAmount),0,SUM(TrnTable.TrnAmount),0)
0
 
ClarionAuthor Commented:
Why does SUM(TrnTable.TrnAmount),0 have to be repeated twice?
0
 
venkotchCommented:
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
 
ClarionAuthor Commented:
Thanks very much...

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now