Re: Help: Teradata Case Converted to Oracle Decode

In article <3bf567bd.17090205_at_news>, jpquinn_at_home.com says...
>
>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

Why not just use CASE? Its available in Oracle8i release 2 (816) and up....

In decode, you would:

select price_plan_code,
SUM( decode( cycle_run_month || cycly_run_year, '082001', reported_time_dur, 0 ) aug_usage,
SUM( decode( cycle_run_month || cycly_run_year, '092001', reported_time_dur, 0 ) sep_usage,
FROM LD_ACCESS_VIEWS.VSLU001_USAGE_DETAIL GROUP BY price_plan_code
ORDER BY 1

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp