Sabbarrao-
You still haven't removed the to_char() from the places I indicated...
Try this:

select to_char(Hour_of_Day,'HH AM'),Amount from ( select ba_timestamp
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')
group by ba_timestamp
)
order by Hour_of_Day;

This experiment worked for me...

select to_char(credt, 'MM-DD-YYYY HH24:MI:SS') from (select
creation_date credt, count(*) cnt
from conc_requests
group by creation_date)
order by credt
/

-----Original Message-----
From: subbarao
[mailto:oracledba-ezmlmshield-x18194535.[Email address protected]
Sent: Thursday, January 27, 2005 11:12 AM
To: LazyDBA Discussion
Subject: RE: Help in SQL Query - TIMESTAMP

Correction to the View:

Here is the correct view:
---------------------------

create view test
as
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')
group by to_char(ba_timestamp,'HH AM');

Sorry for the confusion caused.

-Subbarao

Quoting [Email address protected]

> Hi Chris:
>
> Thanks a lot for your valuable feedback. I am looking for a solution
> which eliminates the use of the view.
>
> Here is what I am looking for as indicated earlier. ( The below
> results are when I used a view. But, I am looking for a solution in
> which I can eliminate using the view )
>
> 1) View:
> --------
>
> create view test
> as
> 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')
> order by to_date(to_char(ba_timestamp,'HH AM'));
>
> 2) Query against the View:
> --------------------------
>
> select hour_of_day,amount from test
> order by to_date(hour_of_day,'hh am');
>
> 3) Result:
> ----------
>
> SQL> select hour_of_day,amount from test
> 2 order by to_date(hour_of_day,'hh am');
>
> HOUR_ AMOUNT
> ----- ----------
> 12 AM 11111
> 05 AM 11111
> 06 AM 11111
> 10 AM 5
> 11 AM 15.01
> 12 PM 75.68
> 01 PM 15.22
> 02 PM 432
> 03 PM 48.87
> 04 PM 263
> 05 PM 11111
>
> 11 rows selected.
>
> - Here is the result when I used your suggested query and I wanted the

> results per hour and so it did not help me.( That means, I should not
> see more than
> 24
> Records out of the query )
>
> SQL> select to_char(Hour_of_Day,'HH AM'),Amount
> 2 from
> 3 (
> 4 select ba_timestamp Hour_of_Day,
> 5 sum(decode(ba_status,'bdone',decode
(ba_price,null,0,ba_price),0))
> 6 Amount from bill_auth0 where ba_status in ('bdone') and
srvc_prod_id =
> 7 'TOPUP' and merchant_id not in ('MoreMagic', 'cwcayman') and
ba_channel
> 8 in
> ('SMS','WEB','WAP','ATM','J2ME')
> group by ba_timestamp
> )
> order by Hour_of_Day; 9 10 11 12
>
> TO_CH AMOUNT
> ----- ----------
> 12 AM 11111
> 06 AM 11111
> 05 PM 11111
> 05 AM 11111
> 10 AM 5
> 11 AM 5.01
> 11 AM 10
> 12 PM 5
> 12 PM 5
> 12 PM 10
> 12 PM 5
>
> TO_CH AMOUNT
> ----- ----------
> 12 PM 5.02
> 12 PM 5
> 12 PM 5.22
> 02 PM 5
> 03 PM 13.27
> 12 PM 10
> 12 PM 5
> 12 PM 5.22
> 12 PM 5.22
> 12 PM 5
> 12 PM 5
>
> TO_CH AMOUNT
> ----- ----------
> 01 PM 5.22
> 03 PM 5
> 03 PM 10
> 03 PM 5.1
> 03 PM 5
> 03 PM 10.5
> 01 PM 10
> 02 PM 10
> 02 PM 34
> 02 PM 123
> 02 PM 120
>
> TO_CH AMOUNT
> ----- ----------
> 02 PM 30
> 02 PM 110
> 04 PM 12
> 04 PM 111
> 04 PM 120
> 04 PM 20
>
> 39 rows selected.
>
> 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


--
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


Oracle LazyDBA home page