Re: Converting Teradata Case to Oracle Decode

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 17 Nov 2001 02:34:05 GMT
Message-ID: <x0kJ7.1916$Hk5.410457175@newssvr30.news.prodigy.com>

the "group by" clause must contain all of the non-aggregate expressions in the select list.

oracle doesn't support the "named" clause like teradata does. and you can't refer to an expression with a number in the "group by" clause (like you can in the "order by" clause.) and, as you've discovered, the "case" expression is not supported either. at least not in version 8.0 or earlier.

what you can do is nest the decode statements; this may work in your case.

i recall there is an issue with the datatypes of the expressions in the decode having to match; there's a problem if they don't.

anyway, i've not tested this, but it may get you started...

SELECT PRICE_PLAN_CODE

     , SUM(DECODE(CYCLE_RUN_MONTH,'08'
          ,DECODE(CYCLE_RUN_YEAR,'2001',REPORTED_TIME_DUR
          ,0),0)) AUG_USAGE
     , SUM(DECODE(CYCLE_RUN_MONTH,'09'
          ,DECODE(CYCLE_RUN_YEAR,'2001',REPORTED_TIME_DUR
          ,0),0)) SEP_USAGE

  FROM LD_ACCESS_VIEWS.VSLU001_USAGE_DETAIL  GROUP BY PRICE_PLAN_CODE
 ORDER BY 1 -or-

SELECT PRICE_PLAN_CODE

     , SUM(DECODE(CYCLE_RUN_MONTH||CYCLE_RUN_YEAR,'082001'
          ,REPORTED_TIME_DUR,0) AUG_USAGE
     , SUM(DECODE(CYCLE_RUN_MONTH||CYCLE_RUN_YEAR,'092001'
          ,REPORTED_TIME_DUR,0) SEP_USAGE

  FROM LD_ACCESS_VIEWS.VSLU001_USAGE_DETAIL  GROUP BY PRICE_PLAN_CODE
 ORDER BY 1 HTH "Jay Quinn" <jpquinn_at_home.com> wrote in message news:3bf566d6.16858576_at_news...
> Hi, here is my Teradata SQL that uses a CASE statement which runs
> fine.
>
> select price_plan_code,
> SUM(CASE WHEN CYCLE_RUN_MONTH = '08' AND CYCLE_RUN_YEAR = '2001' THEN
> REPORTED_TIME_DUR ELSE 0 END) AUG_USAGE,
> SUM(CASE WHEN CYCLE_RUN_MONTH = '09' AND CYCLE_RUN_YEAR = '2001' THEN
> REPORTED_TIME_DUR ELSE 0 END) SEP_USAGE
> FROM LD_ACCESS_VIEWS.VSLU001_USAGE_DETAIL
> GROUP BY 1
> ORDER BY 1
>
> Here is my Oracle SQL with a Decode statement thus far. It gives me
> the error, line 2: not a GROUP BY expression., I know it is though.
>
> select u.price_plan_code,
> decode (u.cycle_run_month, '08', u.reported_time_dur),
> decode (u.cycle_run_year, '2001', u.reported_time_dur) Aug_Usage,
> decode (u.cycle_run_month, '09', u.reported_time_dur),
> decode (u.cycle_run_year, '2001, u.reported_time_dur) Sep_Usage
> FROM US0108 u
> where u.cycle_code = '1'
> and u.cycle_run_month = '08'
> and u.cycle_run_year = '2001'
> GROUP BY u.price_plan_code
> ORDER BY u.price_plan_code
> /
>
> I need the Oracle Decode Syntax, if anyone could please share it with
> me and take a look at this code and recommend some changes.
>
> Thanks, Jay
Received on Fri Nov 16 2001 - 20:34:05 CST