0

I have this line I am struggling with to convert a query from Oracle to SQL Server 2012. the following line is:

DECODE(SUM(DECODE(a.canceldate, NULL, 1,0)), 1, NULL, To_Date(MAX(TO_CHAR(a.canceldate,'yyyymmdd')), 'yyyymmdd')) dCancelDate,

As I inteprete is to convert it like:

case a.canceldate 
   (when sum(case a.canceldate when Null then 1 else 0 end)) 
   when 1 
       then 0  
       else convert(datetime,a.canceldate) 
end max(a.canceldate) as dCancelDate,

I will appreciate some assistant, my line is not correct for SQL Server 2012.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Joel Jacobson
  • 145
  • 3
  • 15

1 Answers1

1

The decode formula is equivalent to

case sum(case when a.canceldate is null then 1 else 0 end) when 1 then null
     else to_date( ... )    end dCancelDate, ...

One mistake I saw in your translation is that you have when sum(...) when 1. You can't have it both ways, it is either when sum(...) = 1 or sum(...) when 1. It may be the only mistake, I didn't look too hard.

What you have within the to_date() is horrible; are you converting dates to character strings, then take the max IN ALPHABETICAL ORDER and then translate back to date? Why? Perhaps just so you delete the time-of-day component? That is a lot easier done with trunc(max(a.canceldate)).

  • @JoelJacobson - the two possible syntaxes for `case`, one with a logical condition (like an `=` but it can be other things too, like `is null`) and the other just with values to compare to, are called "**simple** `case` expression" and "**searched** `case` expression", at least in Oracle. Not sure if SQL Server supports both; I used both in my translation. If it doesn't work as-is, try to use only the "simple" syntax. –  Feb 02 '17 at 23:47
  • So I gave it a shot, but `trunc(max(a.canceldate))` doesn't recognize the function, I try not sure the originator code person from oracle, but I just need to convert it as it is thank you. – Joel Jacobson Feb 03 '17 at 00:29
  • 1
    @JoelJacobson - Did the `case` expression translation work? If you can't test due to the `max(...)` (date computation), replace that part with a simple date (like SYSDATE in Oracle, not sure what SQL Server has) just to see if the translation of `decode` is correct. If you need further help on the date computation, perhaps that's a separate question you can post here (not related to translating DECODE to CASE). –  Feb 03 '17 at 00:34
  • the date magic in the second part of the expression can be solved without string conversion, see http://stackoverflow.com/a/113055/21336 – devio Feb 03 '17 at 07:57
  • Thank you it worked for me, I replace the trunk for : `case sum(case when a.canceldate is null then 1 else 0 end) when 1 then null else max(a.canceldate) end,` – Joel Jacobson Feb 03 '17 at 17:15
  • @JoelJacobson - I hope you didn't spell `trunk` like that? In SQL Server, like in Oracle, the spelling is **`trunc`**, and the function should work similarly (see the documentation for any differences). –  Feb 03 '17 at 17:40