# DECODE question

Hi,

I would like how to translate the Ms Access SQL to Oracle SQL and use DECODE function:

I have

RESULT1 : sum(decode(null,field1,0,field2,0,field3,0,1))

I need to translate this :

Sum (  IIf ( DateDiff("n",[DATE1], [DATE2] < 0.01, 1, 0) ) / ( IIf([RESULT1]=0, 1, [RESULT1])  )

Here the DateDiff function in Ms Access mean the Difference between DATE1 and DATE2 in number of MINUTES.

Thank a lot.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Let's work outward from the middle.

Difference between dates: (Date2 - Date1) = number of DAYS, including fractions.

Difference between dates, expressed as minutes: ((Date2-Date1)*24*60) Note that if Date1 is greater, the value will be negative.  To get an unsigned value, use ABS(...), the Absolute Value function, as in ABS((Date2-Date1)*24*60).

I cannot tell what you're trying to do with your equation, nor what RESULT1 is.
Let ABS((Date2-Date1)*24*60) = 'x'.
Maybe you want this: SUM ( decode ( CEIL(x*100),1,1,0 ) ) / DECODE ( Result1, 0, 1, Result1 )

The syntax of DECODE is DECODE (source_expression, value_1, map_to_1, value_2, map_to_2 , ... , value_n, map_to_n, Default_value )

Where each Value_N is replaced (if an exact match) by Map_To_N.  No inequalities allowed!  That's why I had to use CEIL ( x * 100 ) -- if x is <= .01, then CEIL ( x * 100 ) = 1; for values greater than .01 it'll return 2 or greater.

Let me know if this works for you or not.
-tbcox@att.net
0

Experts Exchange Solution brought to you by