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

DECODE question


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.
1 Solution
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.
sum(decode(sign(abs(DATE1 - DATE2)*1440 - 0.01)),-1, 1,0)/
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.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

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

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