Chris is right, but you also need a from clause.
--Garry
-----Original Message-----
From: Chris Hagemaier
[mailto:oracledba-ezmlmshield-x94453646.[Email address protected]
Sent: Wednesday, January 26, 2005 2:25 PM
To: LazyDBA Discussion
Subject: RE: Help in SQL Query - TIMESTAMP
Subbarao-
Your group by clause has to match your first column
or vice versa (that's why the error). Also you can simplify
this just a bit by replacing:
sum(decode(ba_status,'bdone',decode(ba_price,null,0,ba_price),0))
with:
sum(decode(ba_status,'bdone',nvl(ba_price,0),0))
Chris
-----Original Message-----
From: subbarao
[mailto:oracledba-ezmlmshield-x92257460.[Email address protected]
Sent: Wednesday, January 26, 2005 12:37 PM
To: LazyDBA Discussion
Subject: Re: Help in SQL Query - TIMESTAMP
Hi Mirhashimali:
Thanks for your help. But, I need your help in getting the records in
the required format.
SQL> select to_char (ba_timestamp,'MM-DD-YYYY HH:MI:SS AM') Hour_of_Day,
4 In ('bdone') and srvc_prod_id = 'TOPUP' and merchant_id not in
5 ('MoreMagic',
6 'cwcayman') and ba_channel in ('SMS','WEB','WAP','ATM','J2ME') and
7 ba_timestamp between to_date(
8 '"+sdate+"', 'MM/DD/YYYY' ) and to_date( '"+edate+"', 'MM/DD/YYYY'
)
9 group by to_char(ba_timestamp,'HH AM') 10 order by to_char
(ba_timestamp,'MM-DD-YYYY HH24:MI:SS AM'); select to_char
(ba_timestamp,'MM-DD-YYYY HH:MI:SS AM') Hour_of_Day,
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
If I run the below Query, it works :
SQL> select to_char(ba_timestamp, 'yyyymmdd hh24miss') Hour_of_Day,
2 sum(decode(ba_status,'bdone',decode (ba_price,null,0,ba_price),0))
Amount
3 from bill_auth0 where ba_status in ('bdone') and srvc_prod_id =
'TOPUP' and
4 merchant_id not in ('MoreMagic', 'cwcayman') and ba_channel in
5 ('SMS','WEB','WAP','ATM','J2ME')
6 group by to_char(ba_timestamp, 'yyyymmdd hh24miss')
7 order by to_char(ba_timestamp, 'yyyymmdd hh24miss');
HOUR_OF_DAY AMOUNT
--------------- ----------
20041222 105300 5
20041222 114905 5.01
20041222 115146 10
20041222 120638 5
20041222 120800 5
20041222 121423 10
20041222 121653 5
20041222 121922 5.02
20041222 122343 5
But, as I requested, I wanted the "HOUR_OF_DAY" field to be in AM/PM
format.
I mean , I need the output like below:
HOUR_OF_DAY Amount
---------------------- -----------
01-14-2005 04:22:34 PM 5
01-14-2005 04:22:49 PM 10.05
01-14-2005 04:22:50 PM 15.11
01-14-2005 04:22:59 PM 5.55
Appreciate your help.
Thanks
-Subbarao
Quoting "Mir M. Mirhashimali "
<oracledba-ezmlmshield-x94493170.[Email address protected]
> order by to_char(ba_timestamp, 'yyyymmddhh24miss')
>
> Mir M. Mirhashimali
> Oracle Systems Manager
> Web Services, Rice University
> (713) 348-6365
>
>
>
> subbarao wrote:
>
> >Hi:
> >
> >Could some one help me in getting the records order by TIMESTAMP.
> >
> >BA_TIMESTAMP is a DATE filed.
> >
> >The issue is, we are seeing records with PM coming ahead of earlier
> >TIME
> AM.
> >
> >Example: A Record with a TIME of 5 PM is ahead of 6 AM and we wanted
> >to get
> the
> >records in sequence. Like 1 AM , 2 AM ....... 1PM , 2PM , 3 PM ....
> >
> >Here are the Queries we use and I would appreciate the help.
> >
> >select to_char (ba_timestamp,'HH AM') Hour_of_Day,
> >sum(decode(ba_status,'bdone',decode
> >(ba_price,null,0,ba_price),0)) Amount from bill_auth0 where ba_status
> >in
> >('bdone') and
> >srvc_prod_id = 'TOPUP' and merchant_id not in ('MoreMagic',
> >'cwcayman') and ba_channel in
> >('SMS','WEB','WAP','ATM','J2ME') and ba_timestamp between to_date(
> '"+sdate+"',
> >'MM/DD/YYYY' ) and to_date( '"+edate+"', 'MM/DD/YYYY' ) group by
> >to_char(ba_timestamp,'HH
> >AM') order by Hour_of_Day;
> >
> >
> >
> >SELECT to_char(ba_timestamp,'HH AM')||' -
> >'||to_char(ba_timestamp+1/24,'HH
> AM')
> >"Hour_of_Day", sum(decode(ba_status,'bdone',decode
> (ba_price,null,0,ba_price),0))
>
>"Amount",trunc(decode(sum(decode(ba_status,'bdone',1,0)),0,0,(sum(decod
e(ba_status,'bdone'
> >,decode(ba_price,null,0,ba_price),0))/sum(decode(ba_status,'bdone',1,
> >0)))),2)
> >"Average",count(*) "Number_of_topup_attempts" from bill_auth0 where
> ba_status in
> >('bdone')
> >and srvc_prod_id = 'TOPUP' and merchant_id not in ('MoreMagic',
> >'cwcayman')
> and
> >ba_channel
> >in ('SMS','WEB','WAP','ATM','J2ME') and ba_timestamp between to_date(
> >$P!{StartDate}, 'MM/DD/YYYY' ) and to_date( $P!{EndDate},
> >'MM/DD/YYYY' ) group by to_char(ba_timestamp,'HH AM')||' -
> >'||to_char(ba_timestamp+1/24,'HH AM');
> >
> >Thanks
> >-Subbarao
> >
> >
> >
> >
> >
> >
> >--------
> >website: http://www.LazyDBA.com
> >Please don't reply to RTFM questions
> >Oracle documentation is here: http://tahiti.oracle.com To
> >unsubscribe: see http://www.lazydba.com/unsubscribe.html
> >To subscribe: see http://www.lazydba.com By using this list you
> >agree to these
> terms:http://www.lazydba.com/legal.html
> >
> >
> >!DSPAM:41eec1fd96832452016434!
> >
> >.
> >
> >
> >
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
> see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com By using this list you agree
> to these terms:http://www.lazydba.com/legal.html
>
>
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html
--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.
Douglas County, Oregon
www.co.douglas.or.us
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page