The revolutionary project management tool is here! Plan visually with a single glance and make sure your projects get done.

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.

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,f

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Tackle projects and never again get stuck behind a technical roadblock.

Join Now
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