make that
SELECT u.PRICE_PLAN_CODE
, SUM(DECODE(u.CYCLE_RUN_MONTH,'08'
,DECODE(u.CYCLE_RUN_YEAR,'2001',u.REPORTED_TIME_DUR
,0),0)) As aug_usage
, SUM(DECODE(u.CYCLE_RUN_MONTH,'09'
,DECODE(u.CYCLE_RUN_YEAR,'2001',u.REPORTED_TIME_DUR
,0),0)) As sep_usage
FROM US0108 u
GROUP BY u.PRICE_PLAN_CODE
ORDER BY 1
-or-
SELECT u.PRICE_PLAN_CODE
, SUM(DECODE(u.CYCLE_RUN_MONTH||u.CYCLE_RUN_YEAR
,'082001',u.REPORTED_TIME_DUR,0) As aug_usage
, SUM(DECODE(u.CYCLE_RUN_MONTH||u.CYCLE_RUN_YEAR
,'092001',u.REPORTED_TIME_DUR,0) As sep_usage
FROM US0108 u
GROUP BY u.PRICE_PLAN_CODE
ORDER BY 1
"Spencer" <spencerp_at_swbell.net> wrote in message
news:x0kJ7.1916$Hk5.410457175_at_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:38:45 CST
