A reader, March 12, 2020 - 3:50 pm UTC
Thanks Chris for your reply.
I would use Pivot as Sum not working as expected. It shows the same results as Count data.
I have to also display the amount in USD so I have to join gl_daily_rates a, gl_daily_conversion_types b tables for conversion. So how can I achieve that?
A reader, March 12, 2020 - 4:24 pm UTC
I tried to modify the query with this but not working. Can you please help ?
with rws as (
select org_name, to_char(er.creation_date,'YYYY-MM') dt, ER_TOTAL,
DECODE(er.reim_curr_code,'USD',NVL(ER_TOTAL,0),(select (ER_TOTAL)*(round(MAX(a.conversion_rate),2))
from gl_daily_rates a, gl_daily_conversion_types b
where to_currency = 'USD'
and a.conversion_type = b.conversion_type
and b.user_conversion_type = 'Treasury'
and TRUNC(conversion_date) = TRUNC(A.creation_date)
and er.reimbursement_currency_code=FROM_CURRENCY
GROUP BY a.conversion_type,to_currency))total_USD
from table1 er,table2 etl
where er.ORG_ID = etl.organization_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
)
select * from rws
pivot (
sum( ER_TOTAL) for dt in
('2018-06' "Jun-18", '2018-07' "Jul-18", '2018-08' "Aug-18",'2018-09' "Sep-18", '2018-10' "Oct-18",'2018-11' "Nov-18",'2018-12' "Dec-18",'2019-01' "Jan-19",'2019-02' "Feb-19",
'2019-03' "Mar-19",'2019-04' "Apr-19",'2019-05' "May-19")
)
March 13, 2020 - 11:36 am UTC
Hmmm, that query is a lot more complex than originally suggested...
Please provide example data (create table + inserts) that shows the problem and the results you want based on these data.
A reader, March 13, 2020 - 1:33 pm UTC
I have to convert all the amount in USD so using gl_Daily_rates table to join it but query is very slow now due to this.
This query works fine for 1 or 2 BU but if I run it for all BUs then it gives me timeout error-
Is there any possibility to tune below query?
with rws as (
select org_name, to_char(er.creation_date,'YYYY-MM') dt,
DECODE(er.reim_curr_code,'USD',NVL(ER_TOTAL,0),(select (ER_TOTAL)*(round(MAX(a.conversion_rate),2))
from gl_daily_rates a, gl_daily_conversion_types b
where to_currency = 'USD'
and a.conversion_type = b.conversion_type
and b.user_conversion_type = 'Treasury'
and TRUNC(conversion_date) = TRUNC(A.creation_date)
and er.reimbursement_currency_code=FROM_CURRENCY
GROUP BY a.conversion_type,to_currency))total_USD
from table1 er,table2 etl
where er.ORG_ID = etl.organization_id
AND etl.LANGUAGE='US'
and etl.effective_end_date> sysdate
)
select * from rws
pivot (
sum( total_USD) for dt in
('2018-06' "Jun-18", '2018-07' "Jul-18", '2018-08' "Aug-18",'2018-09' "Sep-18", '2018-10' "Oct-18",'2018-11' "Nov-18",'2018-12' "Dec-18",'2019-01' "Jan-19",'2019-02' "Feb-19",
'2019-03' "Mar-19",'2019-04' "Apr-19",'2019-05' "May-19")
)
March 13, 2020 - 4:23 pm UTC
As stated in my previous reply, please give us a complete test case:
- Create tables
- Insert intos
- Expected output based on these data
As you've asked about performance, please also supply the
execution plan NOT EXPLAIN for the query.
Get this using:
set serveroutput off
select /*+ gather_plan_statistics */* from ...
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));