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




Oracle LazyDBA home page