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
Oracle LazyDBA home page