[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

Display Oracle SQL Data using Cursor, and produce file (csv format).

Hi Expert,
Im trying to display report in following format
Pls see the attachment.
My question is
1- I used CUBE to display data  by merchant_date, Merchant ID, then total by Date and Merchant.How I can print the lines which shows the output of Merchant DATE, MerchantID ?

I 'm trying but cant print data as per requirement.
My Cursor code is

DECLARE
      CURSOR dccReport IS select "Merchant Settlement Date",MERCHANT_ID,ITEM,AUD,CAD,CZK,DKK,GBP,HUF,JPY,LVL,LTL,NZD,NOK,PLN,RUB,ZAR,SEK,CHF,TRY,USD,TOTAL from testreport;
        myReport dccReport%ROWTYPE;
row number :=1;
TB constant varchar2(2):=CHR(09); -- equal to TAB
begin
     open dccReport;
     fetch dccReport into myReport;
       --dbms_output.put_line('Dept'||TB||'Emp'||TB||rpad('Employee',15,' ')||TB||' Salary');
     --dbms_output.put_line('No.'||TB||'No.'||TB||rpad('Name',15,' '));
     --dbms_output.put_line(rpad('-',42,'-'));
     dbms_output.put_line('Merchant' ||TB);
       dbms_output.put_line('Settlement Date ' ||TB||TB||'Merchant ID        '||TB||'Class             '||TB||TB||' AUD '||TB||TB||' CAD '||TB||TB||' CZX '||TB||TB||' DKK '||TB||TB||' GBP '||TB||TB||' ISK '||TB||TB||
                            ' HUF '||TB||TB||' JPY '||TB||TB||' LVL '||TB||TB||' LTL '||TB||TB||' NSD '||TB||TB||' NOK '||TB||TB||' PLN '||TB||TB||' RUB '||TB||TB||' ZAR '||TB||TB||' SEK '||TB||TB||' CHF '||TB||TB||
                                      ' TRY '||TB||TB||' USD '||TB||TB||' Total      ');
       dbms_output.put_line(rpad('-',410,'-'));
              loop
           if dccReport%FOUND then
           dbms_output.put_line(myReport."Merchant Settlement Date" ||TB||TB||TB||TB||myReport.MERCHANT_ID||TB||TB||myReport.ITEM||TB||TB||myReport.AUD||TB||TB||
                                    myReport.CAD||TB||TB||myReport.CZK||TB||TB||myReport.DKK||TB||TB||myReport.GBP||TB||TB||myReport.HUF||TB||TB||
                                                myReport.JPY||TB||TB||myReport.LVL||TB||TB||myReport.LTL||TB||TB||myReport.NZD||TB||TB||myReport.NOK||TB||myReport.PLN||TB||TB||
                                                myReport.RUB||TB||TB||myReport.ZAR||TB||TB||myReport.SEK||TB||TB||myReport.CHF||TB||TB||myReport.TRY||TB||TB||myReport.USD||TB||TB||
                                                myReport.TOTAL);
           fetch dccReport into myReport;
           row := row + 1;
         elsif dccReport%NOTFOUND then
          exit;
         end if;
       end loop;
       if dccReport%ISOPEN then
         close dccReport;
       end if;
end;


Many Thank.
cursor-data.txt
0
adnankh
Asked:
adnankh
  • 9
  • 8
  • 2
10 Solutions
 
slightwv (䄆 Netminder) Commented:
If I understand what you are after, you want everything to line up?

If so:  You need to PAD the null values and fields to their maximum widths.

So if merchant_id is null and 30 characters maximum:

... || rpad(nvl(myReport.MERCHANT_ID,' '), 30 ,' ')|| ...
0
 
lwadwellCommented:
Are you after a CSV (tab delimited) or a formatted TXT?
0
 
adnankhAuthor Commented:
CSV Fotrmat..

Thank you
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
Then change the constant from Chr(9) which is a tab to a ','.

|| column_name ||',' || next_column ||',' ...
0
 
lwadwellCommented:
Do not put multiple delimiters, whether comma or tab, between columns.

What it your expected result?  ... what specifically is your problem?
0
 
adnankhAuthor Commented:
Hi Expert,

Here is whole story.

I need to generate report in CSV format from the oracle pl/sql cursor or whatever best option is. The detail test report is attached. Because I'm new this is area that's a bit struggling to do it..What I need to do selecting data from master table and populate into this testreport table using one cursor, and same cursor create/write this report to some location. This is my whole scenario.  
CREATE TABLE TESTREPORT
(
  "Merchant Settlement Date"  VARCHAR2(9 BYTE),
  MERCHANT_ID                 VARCHAR2(15 BYTE),
  ITEM                        CHAR(11 BYTE),
  AUD                         NUMBER,
  CAD                         NUMBER,
  CZK                         NUMBER,
  DKK                         NUMBER,
  GBP                         NUMBER,
  HUF                         NUMBER,
  JPY                         NUMBER,
  LVL                         NUMBER,
  LTL                         NUMBER,
  NZD                         NUMBER,
  NOK                         NUMBER,
  PLN                         NUMBER,
  RUB                         NUMBER,
  ZAR                         NUMBER,
  SEK                         NUMBER,
  CHF                         NUMBER,
  TRY                         NUMBER,
  USD                         NUMBER,
  TOTAL                       NUMBER
)  

I'm using this sql to generate this report from the master table, but want to use same above cursor.

break on merchant_id
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') "Merchant Settlement Date", merchant_id,'1-DCC value' item,  
      sum(aud) aud, sum(cad) cad,sum(czk) czk,sum(dkk) dkk, sum(gbp) gbp,sum(huf) huf,sum(jpy) jpy,sum(lvl) lvl,sum(ltl) ltl,sum(nzd) nzd,sum(nok) nok,sum(pln) pln,sum(rub) rub,sum(zar) zar,sum(sek) sek,sum(chf) chf,sum(try) try,sum(usd) usd,
      sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
      from (
                  select dcc_prov_settlbatch_no, merchant_id,
                   case when FOREIGN_CURRENCY_NUMBER = 36  then FOREIGN_CURRENCY_AMOUNT else 0 end AUD,
                     case when FOREIGN_CURRENCY_NUMBER = 124 then FOREIGN_CURRENCY_AMOUNT else 0 end CAD,
                     case when FOREIGN_CURRENCY_NUMBER = 203 then FOREIGN_CURRENCY_AMOUNT else 0 end CZK,
                     case when FOREIGN_CURRENCY_NUMBER = 208 then FOREIGN_CURRENCY_AMOUNT else 0 end DKK,
                     case when FOREIGN_CURRENCY_NUMBER = 826 then FOREIGN_CURRENCY_AMOUNT else 0 end GBP,
                     case when FOREIGN_CURRENCY_NUMBER = 348 then FOREIGN_CURRENCY_AMOUNT else 0 end HUF,
                     case when FOREIGN_CURRENCY_NUMBER = 392 then FOREIGN_CURRENCY_AMOUNT else 0 end JPY,
                     case when FOREIGN_CURRENCY_NUMBER = 428 then FOREIGN_CURRENCY_AMOUNT else 0 end LVL,
                     case when FOREIGN_CURRENCY_NUMBER = 440 then FOREIGN_CURRENCY_AMOUNT else 0 end LTL,
                     case when FOREIGN_CURRENCY_NUMBER = 554 then FOREIGN_CURRENCY_AMOUNT else 0 end NZD,
                     case when FOREIGN_CURRENCY_NUMBER = 578 then FOREIGN_CURRENCY_AMOUNT else 0 end NOK,
                     case when FOREIGN_CURRENCY_NUMBER = 985 then FOREIGN_CURRENCY_AMOUNT else 0 end PLN,
                     case when FOREIGN_CURRENCY_NUMBER = 643 then FOREIGN_CURRENCY_AMOUNT else 0 end RUB,
                     case when FOREIGN_CURRENCY_NUMBER = 710 then FOREIGN_CURRENCY_AMOUNT else 0 end ZAR,
                     case when FOREIGN_CURRENCY_NUMBER = 752 then FOREIGN_CURRENCY_AMOUNT else 0 end SEK,
                     case when FOREIGN_CURRENCY_NUMBER = 756 then FOREIGN_CURRENCY_AMOUNT else 0 end CHF,
                     case when FOREIGN_CURRENCY_NUMBER = 949 then FOREIGN_CURRENCY_AMOUNT else 0 end TRY,
                     case when FOREIGN_CURRENCY_NUMBER = 840 then FOREIGN_CURRENCY_AMOUNT else 0 end USD
                     from dcct_dtf
                     --where term_settl_batch_no=120530  
                     where dcc_process_status=3
                ) group by (dcc_prov_settlbatch_no,merchant_id)
union         
      select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') Merchant_Date, merchant_id,'2-Original Sale Value EURO' item,  
sum(aud) aud, sum(cad) cad,sum(czk),sum(dkk), sum(gbp),sum(huf),sum(jpy),sum(lvl),sum(ltl),sum(nzd),sum(nok),sum(pln),sum(rub),sum(zar),sum(sek),sum(chf),sum(try),sum(usd),
sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
from (
       select dcc_prov_settlbatch_no, merchant_id,
             case when FOREIGN_CURRENCY_NUMBER = 36  then BASE_CURRENCY_AMOUNT else 0 end AUD,
               case when FOREIGN_CURRENCY_NUMBER = 124 then BASE_CURRENCY_AMOUNT else 0 end CAD,
               case when FOREIGN_CURRENCY_NUMBER = 203 then BASE_CURRENCY_AMOUNT else 0 end CZK,
               case when FOREIGN_CURRENCY_NUMBER = 208 then BASE_CURRENCY_AMOUNT else 0 end DKK,
               case when FOREIGN_CURRENCY_NUMBER = 826 then BASE_CURRENCY_AMOUNT else 0 end GBP,
               case when FOREIGN_CURRENCY_NUMBER = 348 then BASE_CURRENCY_AMOUNT else 0 end HUF,
               case when FOREIGN_CURRENCY_NUMBER = 392 then BASE_CURRENCY_AMOUNT else 0 end JPY,
               case when FOREIGN_CURRENCY_NUMBER = 428 then BASE_CURRENCY_AMOUNT else 0 end LVL,
               case when FOREIGN_CURRENCY_NUMBER = 440 then BASE_CURRENCY_AMOUNT else 0 end LTL,
               case when FOREIGN_CURRENCY_NUMBER = 554 then BASE_CURRENCY_AMOUNT else 0 end NZD,
               case when FOREIGN_CURRENCY_NUMBER = 578 then BASE_CURRENCY_AMOUNT else 0 end NOK,
               case when FOREIGN_CURRENCY_NUMBER = 985 then BASE_CURRENCY_AMOUNT else 0 end PLN,
               case when FOREIGN_CURRENCY_NUMBER = 643 then BASE_CURRENCY_AMOUNT else 0 end RUB,
               case when FOREIGN_CURRENCY_NUMBER = 710 then BASE_CURRENCY_AMOUNT else 0 end ZAR,
               case when FOREIGN_CURRENCY_NUMBER = 752 then BASE_CURRENCY_AMOUNT else 0 end SEK,
               case when FOREIGN_CURRENCY_NUMBER = 756 then BASE_CURRENCY_AMOUNT else 0 end CHF,
               case when FOREIGN_CURRENCY_NUMBER = 949 then BASE_CURRENCY_AMOUNT else 0 end TRY,
               case when FOREIGN_CURRENCY_NUMBER = 840 then BASE_CURRENCY_AMOUNT else 0 end USD
               from dcct_dtf
               --where term_settl_batch_no=120530  
               where dcc_process_status=3
     ) group by (dcc_prov_settlbatch_no,merchant_id)                                      
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') MerchantDate, merchant_id,'3-DCC opt-in performance' item, avg(aud) aus, avg(cad) cad, avg(czk) czk, avg(dkk) dkk, avg(gbp) gbp, avg(huf) huf, avg(jpy) jpy, avg(lvl) lvl, avg(ltl) ltl, avg(nzd) nzd, avg(nok) nok, avg(pln) pln, avg(rub) rub, avg(zar) zar, avg(sek) sek,avg(chf) chf, avg(try) try, avg(usd) usd,
avg(aud)+avg(cad)+avg(czk)+avg(dkk)+ avg(gbp)+avg(huf)+ avg(jpy)+ avg(lvl)+ avg(ltl)+ avg(nzd)+ avg (nok)+ avg(pln)+ avg(rub)+ avg(zar)+ avg(sek)+avg(chf)+ avg(try)+ avg(usd) Total
          from (
               select dcc_prov_settlbatch_no, merchant_id,
                   case when FOREIGN_CURRENCY_NUMBER = 36  then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end AUD,
                     case when FOREIGN_CURRENCY_NUMBER = 124 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CAD,
                     case when FOREIGN_CURRENCY_NUMBER = 203 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CZK,
                     case when FOREIGN_CURRENCY_NUMBER = 208 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end DKK,
                     case when FOREIGN_CURRENCY_NUMBER = 826 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end GBP,
                     case when FOREIGN_CURRENCY_NUMBER = 348 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end HUF,
                     case when FOREIGN_CURRENCY_NUMBER = 392 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end JPY,
                     case when FOREIGN_CURRENCY_NUMBER = 428 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LVL,
                     case when FOREIGN_CURRENCY_NUMBER = 440 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LTL,
                     case when FOREIGN_CURRENCY_NUMBER = 554 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NZD,
                     case when FOREIGN_CURRENCY_NUMBER = 578 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NOK,
                     case when FOREIGN_CURRENCY_NUMBER = 985 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end PLN,
                     case when FOREIGN_CURRENCY_NUMBER = 643 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end RUB,
                     case when FOREIGN_CURRENCY_NUMBER = 710 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end ZAR,
                     case when FOREIGN_CURRENCY_NUMBER = 752 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end SEK,
                     case when FOREIGN_CURRENCY_NUMBER = 756 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CHF,
                     case when FOREIGN_CURRENCY_NUMBER = 949 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end TRY,
                     case when FOREIGN_CURRENCY_NUMBER = 840 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end USD
                     from dcct_dtf
                     --where term_settl_batch_no=120530  
                     where dcc_process_status=3
             ) group by (dcc_prov_settlbatch_no,merchant_id)                               
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') "Merchant Settlement Date", merchant_id,'1-DCC value' item,  
      sum(aud) aud, sum(cad) cad,sum(czk) czk,sum(dkk) dkk, sum(gbp) gbp,sum(huf) huf,sum(jpy) jpy,sum(lvl) lvl,sum(ltl) ltl,sum(nzd) nzd,sum(nok) nok,sum(pln) pln,sum(rub) rub,sum(zar) zar,sum(sek) sek,sum(chf) chf,sum(try) try,sum(usd) usd,
      sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
      from (
                  select dcc_prov_settlbatch_no, merchant_id,
                   case when FOREIGN_CURRENCY_NUMBER = 36  then FOREIGN_CURRENCY_AMOUNT else 0 end AUD,
                     case when FOREIGN_CURRENCY_NUMBER = 124 then FOREIGN_CURRENCY_AMOUNT else 0 end CAD,
                     case when FOREIGN_CURRENCY_NUMBER = 203 then FOREIGN_CURRENCY_AMOUNT else 0 end CZK,
                     case when FOREIGN_CURRENCY_NUMBER = 208 then FOREIGN_CURRENCY_AMOUNT else 0 end DKK,
                     case when FOREIGN_CURRENCY_NUMBER = 826 then FOREIGN_CURRENCY_AMOUNT else 0 end GBP,
                     case when FOREIGN_CURRENCY_NUMBER = 348 then FOREIGN_CURRENCY_AMOUNT else 0 end HUF,
                     case when FOREIGN_CURRENCY_NUMBER = 392 then FOREIGN_CURRENCY_AMOUNT else 0 end JPY,
                     case when FOREIGN_CURRENCY_NUMBER = 428 then FOREIGN_CURRENCY_AMOUNT else 0 end LVL,
                     case when FOREIGN_CURRENCY_NUMBER = 440 then FOREIGN_CURRENCY_AMOUNT else 0 end LTL,
                     case when FOREIGN_CURRENCY_NUMBER = 554 then FOREIGN_CURRENCY_AMOUNT else 0 end NZD,
                     case when FOREIGN_CURRENCY_NUMBER = 578 then FOREIGN_CURRENCY_AMOUNT else 0 end NOK,
                     case when FOREIGN_CURRENCY_NUMBER = 985 then FOREIGN_CURRENCY_AMOUNT else 0 end PLN,
                     case when FOREIGN_CURRENCY_NUMBER = 643 then FOREIGN_CURRENCY_AMOUNT else 0 end RUB,
                     case when FOREIGN_CURRENCY_NUMBER = 710 then FOREIGN_CURRENCY_AMOUNT else 0 end ZAR,
                     case when FOREIGN_CURRENCY_NUMBER = 752 then FOREIGN_CURRENCY_AMOUNT else 0 end SEK,
                     case when FOREIGN_CURRENCY_NUMBER = 756 then FOREIGN_CURRENCY_AMOUNT else 0 end CHF,
                     case when FOREIGN_CURRENCY_NUMBER = 949 then FOREIGN_CURRENCY_AMOUNT else 0 end TRY,
                     case when FOREIGN_CURRENCY_NUMBER = 840 then FOREIGN_CURRENCY_AMOUNT else 0 end USD
                     from dcct_dtf
                     --where term_settl_batch_no=120530  
                     where dcc_process_status=3
                ) group by cube (dcc_prov_settlbatch_no,merchant_id)
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') Merchant_Date, merchant_id,'2-Original Sale Value EURO' item,  
sum(aud) aud, sum(cad) cad,sum(czk),sum(dkk), sum(gbp),sum(huf),sum(jpy),sum(lvl),sum(ltl),sum(nzd),sum(nok),sum(pln),sum(rub),sum(zar),sum(sek),sum(chf),sum(try),sum(usd),
sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
from (
       select dcc_prov_settlbatch_no, merchant_id,
             case when FOREIGN_CURRENCY_NUMBER = 36  then BASE_CURRENCY_AMOUNT else 0 end AUD,
               case when FOREIGN_CURRENCY_NUMBER = 124 then BASE_CURRENCY_AMOUNT else 0 end CAD,
               case when FOREIGN_CURRENCY_NUMBER = 203 then BASE_CURRENCY_AMOUNT else 0 end CZK,
               case when FOREIGN_CURRENCY_NUMBER = 208 then BASE_CURRENCY_AMOUNT else 0 end DKK,
               case when FOREIGN_CURRENCY_NUMBER = 826 then BASE_CURRENCY_AMOUNT else 0 end GBP,
               case when FOREIGN_CURRENCY_NUMBER = 348 then BASE_CURRENCY_AMOUNT else 0 end HUF,
               case when FOREIGN_CURRENCY_NUMBER = 392 then BASE_CURRENCY_AMOUNT else 0 end JPY,
               case when FOREIGN_CURRENCY_NUMBER = 428 then BASE_CURRENCY_AMOUNT else 0 end LVL,
               case when FOREIGN_CURRENCY_NUMBER = 440 then BASE_CURRENCY_AMOUNT else 0 end LTL,
               case when FOREIGN_CURRENCY_NUMBER = 554 then BASE_CURRENCY_AMOUNT else 0 end NZD,
               case when FOREIGN_CURRENCY_NUMBER = 578 then BASE_CURRENCY_AMOUNT else 0 end NOK,
               case when FOREIGN_CURRENCY_NUMBER = 985 then BASE_CURRENCY_AMOUNT else 0 end PLN,
               case when FOREIGN_CURRENCY_NUMBER = 643 then BASE_CURRENCY_AMOUNT else 0 end RUB,
               case when FOREIGN_CURRENCY_NUMBER = 710 then BASE_CURRENCY_AMOUNT else 0 end ZAR,
               case when FOREIGN_CURRENCY_NUMBER = 752 then BASE_CURRENCY_AMOUNT else 0 end SEK,
               case when FOREIGN_CURRENCY_NUMBER = 756 then BASE_CURRENCY_AMOUNT else 0 end CHF,
               case when FOREIGN_CURRENCY_NUMBER = 949 then BASE_CURRENCY_AMOUNT else 0 end TRY,
               case when FOREIGN_CURRENCY_NUMBER = 840 then BASE_CURRENCY_AMOUNT else 0 end USD
               from dcct_dtf
               --where term_settl_batch_no=120530  
               where dcc_process_status=3
     ) group by cube(dcc_prov_settlbatch_no,merchant_id)
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') MerchantDate, merchant_id,'3-DCC opt-in performance' item, avg(aud) aus, avg(cad) cad, avg(czk) czk, avg(dkk) dkk, avg(gbp) gbp, avg(huf) huf, avg(jpy) jpy, avg(lvl) lvl, avg(ltl) ltl, avg(nzd) nzd, avg(nok) nok, avg(pln) pln, avg(rub) rub, avg(zar) zar, avg(sek) sek,avg(chf) chf, avg(try) try, avg(usd) usd,
avg(aud)+avg(cad)+avg(czk)+avg(dkk)+ avg(gbp)+avg(huf)+ avg(jpy)+ avg(lvl)+ avg(ltl)+ avg(nzd)+ avg (nok)+ avg(pln)+ avg(rub)+ avg(zar)+ avg(sek)+avg(chf)+ avg(try)+ avg(usd) Total
          from (
               select dcc_prov_settlbatch_no, merchant_id,
                   case when FOREIGN_CURRENCY_NUMBER = 36  then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end AUD,
                     case when FOREIGN_CURRENCY_NUMBER = 124 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CAD,
                     case when FOREIGN_CURRENCY_NUMBER = 203 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CZK,
                     case when FOREIGN_CURRENCY_NUMBER = 208 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end DKK,
                     case when FOREIGN_CURRENCY_NUMBER = 826 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end GBP,
                     case when FOREIGN_CURRENCY_NUMBER = 348 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end HUF,
                     case when FOREIGN_CURRENCY_NUMBER = 392 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end JPY,
                     case when FOREIGN_CURRENCY_NUMBER = 428 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LVL,
                     case when FOREIGN_CURRENCY_NUMBER = 440 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LTL,
                     case when FOREIGN_CURRENCY_NUMBER = 554 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NZD,
                     case when FOREIGN_CURRENCY_NUMBER = 578 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NOK,
                     case when FOREIGN_CURRENCY_NUMBER = 985 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end PLN,
                     case when FOREIGN_CURRENCY_NUMBER = 643 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end RUB,
                     case when FOREIGN_CURRENCY_NUMBER = 710 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end ZAR,
                     case when FOREIGN_CURRENCY_NUMBER = 752 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end SEK,
                     case when FOREIGN_CURRENCY_NUMBER = 756 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CHF,
                     case when FOREIGN_CURRENCY_NUMBER = 949 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end TRY,
                     case when FOREIGN_CURRENCY_NUMBER = 840 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end USD
                     from dcct_dtf
                     --where term_settl_batch_no=120530  
                     where dcc_process_status=3
             ) group by cube (dcc_prov_settlbatch_no,merchant_id)                               
order by 1,2,3


I hope it will help you guys to understand the picture.

looking forward to your suggestions.

Many Thanks.
Adnan
test-file.xlsx
0
 
slightwv (䄆 Netminder) Commented:
>>What I need to do selecting data from master table and populate into this testreport table using one cursor, and same cursor create/write this report to some location.

Where does the CSV some into the picture?

If the tables are in the same database can you not do a insert into select statement?

insert into testreport(col1, col2,) select col1, col2 from master_table;
0
 
adnankhAuthor Commented:
Where does the CSV some into the picture? No
If the tables are in the same database can you not do a insert into select statement? Yes
insert into testreport(col1, col2,) select col1, col2 from master_table; Yes I want to do INSERT  via same cursor as I want to use for generate the report. this is kind of requirement OR any other better idea?
Thanks
0
 
slightwv (䄆 Netminder) Commented:
I don't understand your last post.

So, you don't need/want a CSV?  Then don't create one.

>> Yes I want to do INSERT  via same cursor as I want to use for generate the report.

Then use the insert into select syntax I posted.

Just add your column names and your existing select statement.  The only thing you need to be aware of is the data types of the table and the select.
0
 
adnankhAuthor Commented:
So, you don't need/want a CSV?  Then don't create one. >> Sorry was hurry .. Yes I do need a OUTPUT of this cursor as a CSV format in some location.
but Im unable to do it.

Kind Regards
0
 
slightwv (䄆 Netminder) Commented:
Why do you need the CSV?

If you have a working select statement and you need to get that output into a TESTREPORT in the same database, you do not need a CSV.


Check out the small test case sort of based on the code you posted above

Create the test table.

You have your massive select.

Just insert into the test table from the massive select.

-- cut down version of your table
CREATE TABLE TESTREPORT
(
  "Merchant Settlement Date"  VARCHAR2(9 BYTE),
  MERCHANT_ID                 VARCHAR2(15 BYTE),
  ITEM                        CHAR(11 BYTE),
  AUD                         NUMBER,
  CAD                         NUMBER
);




--example of your massive select
select '01-Jan-01','id1','item1',1,2 from dual
union
select '02-Feb-02','id2','item2',3,4 from dual
/



--Just combine them

insert into testreport("Merchant Settlement Date", MERCHANT_ID, ITEM, AUD, CAD) 
select '01-Jan-01','id1','item1',1,2 from dual
union
select '02-Feb-02','id2','item2',3,4 from dual
/

select * from testreport;

Open in new window

0
 
adnankhAuthor Commented:
DECLARE
      CURSOR dccReport IS 
	  --select "Merchant Settlement Date",MERCHANT_ID,ITEM,AUD,CAD,CZK,DKK,GBP,HUF,JPY,LVL,LTL,NZD,NOK,PLN,RUB,ZAR,SEK,CHF,TRY,USD,TOTAL from testreport;
	  insert into testreport 
	  ("Merchant Settlement Date",MERCHANT_ID,ITEM,AUD,CAD,CZK,DKK,GBP,HUF,JPY,LVL,LTL,NZD,NOK,PLN,RUB,ZAR,SEK,CHF,TRY,USD,TOTAL)
						 select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') "Merchant Settlement Date", merchant_id,'1-DCC value' item,  
						 sum(aud) aud, sum(cad) cad,sum(czk) czk,sum(dkk) dkk, sum(gbp) gbp,sum(huf) huf,sum(jpy) jpy,sum(lvl) lvl,sum(ltl) ltl,sum(nzd) nzd,sum(nok) nok,sum(pln) pln,sum(rub) rub,sum(zar) zar,sum(sek) sek,sum(chf) chf,sum(try) try,sum(usd) usd,
						 sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
						 from (		 select dcc_prov_settlbatch_no, merchant_id,
		       				  		 case when FOREIGN_CURRENCY_NUMBER = 36  then FOREIGN_CURRENCY_AMOUNT else 0 end AUD,
									 case when FOREIGN_CURRENCY_NUMBER = 124 then FOREIGN_CURRENCY_AMOUNT else 0 end CAD,
			     					 case when FOREIGN_CURRENCY_NUMBER = 203 then FOREIGN_CURRENCY_AMOUNT else 0 end CZK,
			                         case when FOREIGN_CURRENCY_NUMBER = 208 then FOREIGN_CURRENCY_AMOUNT else 0 end DKK,
			   						 case when FOREIGN_CURRENCY_NUMBER = 826 then FOREIGN_CURRENCY_AMOUNT else 0 end GBP,
			   						 case when FOREIGN_CURRENCY_NUMBER = 348 then FOREIGN_CURRENCY_AMOUNT else 0 end HUF,
			   						 case when FOREIGN_CURRENCY_NUMBER = 392 then FOREIGN_CURRENCY_AMOUNT else 0 end JPY,
									 case when FOREIGN_CURRENCY_NUMBER = 428 then FOREIGN_CURRENCY_AMOUNT else 0 end LVL,
			   						 case when FOREIGN_CURRENCY_NUMBER = 440 then FOREIGN_CURRENCY_AMOUNT else 0 end LTL,
			   						 case when FOREIGN_CURRENCY_NUMBER = 554 then FOREIGN_CURRENCY_AMOUNT else 0 end NZD,
			   						 case when FOREIGN_CURRENCY_NUMBER = 578 then FOREIGN_CURRENCY_AMOUNT else 0 end NOK,
			   						 case when FOREIGN_CURRENCY_NUMBER = 985 then FOREIGN_CURRENCY_AMOUNT else 0 end PLN,
			   						 case when FOREIGN_CURRENCY_NUMBER = 643 then FOREIGN_CURRENCY_AMOUNT else 0 end RUB,
			   						 case when FOREIGN_CURRENCY_NUMBER = 710 then FOREIGN_CURRENCY_AMOUNT else 0 end ZAR,
			   						 case when FOREIGN_CURRENCY_NUMBER = 752 then FOREIGN_CURRENCY_AMOUNT else 0 end SEK,
			   						 case when FOREIGN_CURRENCY_NUMBER = 756 then FOREIGN_CURRENCY_AMOUNT else 0 end CHF,
			   						 case when FOREIGN_CURRENCY_NUMBER = 949 then FOREIGN_CURRENCY_AMOUNT else 0 end TRY,
			   						 case when FOREIGN_CURRENCY_NUMBER = 840 then FOREIGN_CURRENCY_AMOUNT else 0 end USD
			   						 from dcct_dtf
									 --where term_settl_batch_no=120530   
			                         where dcc_process_status=3) 
     	                             group by (dcc_prov_settlbatch_no,merchant_id)
                         union	   
	                     select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') Merchant_Date, merchant_id,'2-Original Sale Value EURO' item,  
                         sum(aud) aud, sum(cad) cad,sum(czk),sum(dkk), sum(gbp),sum(huf),sum(jpy),sum(lvl),sum(ltl),sum(nzd),sum(nok),sum(pln),sum(rub),sum(zar),sum(sek),sum(chf),sum(try),sum(usd),
                         sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
                         from ( 	 select dcc_prov_settlbatch_no, merchant_id,
	       				 	  		 case when FOREIGN_CURRENCY_NUMBER = 36  then BASE_CURRENCY_AMOUNT else 0 end AUD,
									 case when FOREIGN_CURRENCY_NUMBER = 124 then BASE_CURRENCY_AMOUNT else 0 end CAD,
 									 case when FOREIGN_CURRENCY_NUMBER = 203 then BASE_CURRENCY_AMOUNT else 0 end CZK,
 									 case when FOREIGN_CURRENCY_NUMBER = 208 then BASE_CURRENCY_AMOUNT else 0 end DKK,
									 case when FOREIGN_CURRENCY_NUMBER = 826 then BASE_CURRENCY_AMOUNT else 0 end GBP,
									 case when FOREIGN_CURRENCY_NUMBER = 348 then BASE_CURRENCY_AMOUNT else 0 end HUF,
									 case when FOREIGN_CURRENCY_NUMBER = 392 then BASE_CURRENCY_AMOUNT else 0 end JPY,
									 case when FOREIGN_CURRENCY_NUMBER = 428 then BASE_CURRENCY_AMOUNT else 0 end LVL,
									 case when FOREIGN_CURRENCY_NUMBER = 440 then BASE_CURRENCY_AMOUNT else 0 end LTL,
									 case when FOREIGN_CURRENCY_NUMBER = 554 then BASE_CURRENCY_AMOUNT else 0 end NZD,
									 case when FOREIGN_CURRENCY_NUMBER = 578 then BASE_CURRENCY_AMOUNT else 0 end NOK,
									 case when FOREIGN_CURRENCY_NUMBER = 985 then BASE_CURRENCY_AMOUNT else 0 end PLN,
									 case when FOREIGN_CURRENCY_NUMBER = 643 then BASE_CURRENCY_AMOUNT else 0 end RUB,
									 case when FOREIGN_CURRENCY_NUMBER = 710 then BASE_CURRENCY_AMOUNT else 0 end ZAR,
									 case when FOREIGN_CURRENCY_NUMBER = 752 then BASE_CURRENCY_AMOUNT else 0 end SEK,
									 case when FOREIGN_CURRENCY_NUMBER = 756 then BASE_CURRENCY_AMOUNT else 0 end CHF,
									 case when FOREIGN_CURRENCY_NUMBER = 949 then BASE_CURRENCY_AMOUNT else 0 end TRY,
									 case when FOREIGN_CURRENCY_NUMBER = 840 then BASE_CURRENCY_AMOUNT else 0 end USD
									 from dcct_dtf
		   --where term_settl_batch_no=120530   
		   		   		where dcc_process_status=3
     					) group by (dcc_prov_settlbatch_no,merchant_id)	 			 		 
						union 
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') MerchantDate, merchant_id,'3-DCC opt-in performance' item, avg(aud) aus, avg(cad) cad, avg(czk) czk, avg(dkk) dkk, avg(gbp) gbp, avg(huf) huf, avg(jpy) jpy, avg(lvl) lvl, avg(ltl) ltl, avg(nzd) nzd, avg(nok) nok, avg(pln) pln, avg(rub) rub, avg(zar) zar, avg(sek) sek,avg(chf) chf, avg(try) try, avg(usd) usd, 
avg(aud)+avg(cad)+avg(czk)+avg(dkk)+ avg(gbp)+avg(huf)+ avg(jpy)+ avg(lvl)+ avg(ltl)+ avg(nzd)+ avg (nok)+ avg(pln)+ avg(rub)+ avg(zar)+ avg(sek)+avg(chf)+ avg(try)+ avg(usd) Total 
	    from ( select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end AUD, 
			   case when FOREIGN_CURRENCY_NUMBER = 124 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3
             ) group by (dcc_prov_settlbatch_no,merchant_id)					 
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') "Merchant Settlement Date", merchant_id,'1-DCC value' item,  
	sum(aud) aud, sum(cad) cad,sum(czk) czk,sum(dkk) dkk, sum(gbp) gbp,sum(huf) huf,sum(jpy) jpy,sum(lvl) lvl,sum(ltl) ltl,sum(nzd) nzd,sum(nok) nok,sum(pln) pln,sum(rub) rub,sum(zar) zar,sum(sek) sek,sum(chf) chf,sum(try) try,sum(usd) usd,
	sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
	from (select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then FOREIGN_CURRENCY_AMOUNT else 0 end AUD,
			   case when FOREIGN_CURRENCY_NUMBER = 124 then FOREIGN_CURRENCY_AMOUNT else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then FOREIGN_CURRENCY_AMOUNT else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then FOREIGN_CURRENCY_AMOUNT else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then FOREIGN_CURRENCY_AMOUNT else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then FOREIGN_CURRENCY_AMOUNT else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then FOREIGN_CURRENCY_AMOUNT else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then FOREIGN_CURRENCY_AMOUNT else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then FOREIGN_CURRENCY_AMOUNT else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then FOREIGN_CURRENCY_AMOUNT else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then FOREIGN_CURRENCY_AMOUNT else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then FOREIGN_CURRENCY_AMOUNT else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then FOREIGN_CURRENCY_AMOUNT else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then FOREIGN_CURRENCY_AMOUNT else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then FOREIGN_CURRENCY_AMOUNT else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then FOREIGN_CURRENCY_AMOUNT else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then FOREIGN_CURRENCY_AMOUNT else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then FOREIGN_CURRENCY_AMOUNT else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3
     	     ) group by cube (dcc_prov_settlbatch_no,merchant_id)
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') Merchant_Date, merchant_id,'2-Original Sale Value EURO' item,  
sum(aud) aud, sum(cad) cad,sum(czk),sum(dkk), sum(gbp),sum(huf),sum(jpy),sum(lvl),sum(ltl),sum(nzd),sum(nok),sum(pln),sum(rub),sum(zar),sum(sek),sum(chf),sum(try),sum(usd),
sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
from (select dcc_prov_settlbatch_no, merchant_id,
	       case when FOREIGN_CURRENCY_NUMBER = 36  then BASE_CURRENCY_AMOUNT else 0 end AUD,
		   case when FOREIGN_CURRENCY_NUMBER = 124 then BASE_CURRENCY_AMOUNT else 0 end CAD,
		   case when FOREIGN_CURRENCY_NUMBER = 203 then BASE_CURRENCY_AMOUNT else 0 end CZK,
		   case when FOREIGN_CURRENCY_NUMBER = 208 then BASE_CURRENCY_AMOUNT else 0 end DKK,
		   case when FOREIGN_CURRENCY_NUMBER = 826 then BASE_CURRENCY_AMOUNT else 0 end GBP,
		   case when FOREIGN_CURRENCY_NUMBER = 348 then BASE_CURRENCY_AMOUNT else 0 end HUF,
		   case when FOREIGN_CURRENCY_NUMBER = 392 then BASE_CURRENCY_AMOUNT else 0 end JPY,
		   case when FOREIGN_CURRENCY_NUMBER = 428 then BASE_CURRENCY_AMOUNT else 0 end LVL,
		   case when FOREIGN_CURRENCY_NUMBER = 440 then BASE_CURRENCY_AMOUNT else 0 end LTL,
		   case when FOREIGN_CURRENCY_NUMBER = 554 then BASE_CURRENCY_AMOUNT else 0 end NZD,
		   case when FOREIGN_CURRENCY_NUMBER = 578 then BASE_CURRENCY_AMOUNT else 0 end NOK,
		   case when FOREIGN_CURRENCY_NUMBER = 985 then BASE_CURRENCY_AMOUNT else 0 end PLN,
		   case when FOREIGN_CURRENCY_NUMBER = 643 then BASE_CURRENCY_AMOUNT else 0 end RUB,
		   case when FOREIGN_CURRENCY_NUMBER = 710 then BASE_CURRENCY_AMOUNT else 0 end ZAR,
		   case when FOREIGN_CURRENCY_NUMBER = 752 then BASE_CURRENCY_AMOUNT else 0 end SEK,
		   case when FOREIGN_CURRENCY_NUMBER = 756 then BASE_CURRENCY_AMOUNT else 0 end CHF,
		   case when FOREIGN_CURRENCY_NUMBER = 949 then BASE_CURRENCY_AMOUNT else 0 end TRY,
		   case when FOREIGN_CURRENCY_NUMBER = 840 then BASE_CURRENCY_AMOUNT else 0 end USD
		   from dcct_dtf
		   --where term_settl_batch_no=120530   
		   where dcc_process_status=3
     ) group by cube(dcc_prov_settlbatch_no,merchant_id)
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') MerchantDate, merchant_id,'3-DCC opt-in performance' item, avg(aud) aus, avg(cad) cad, avg(czk) czk, avg(dkk) dkk, avg(gbp) gbp, avg(huf) huf, avg(jpy) jpy, avg(lvl) lvl, avg(ltl) ltl, avg(nzd) nzd, avg(nok) nok, avg(pln) pln, avg(rub) rub, avg(zar) zar, avg(sek) sek,avg(chf) chf, avg(try) try, avg(usd) usd, 
avg(aud)+avg(cad)+avg(czk)+avg(dkk)+ avg(gbp)+avg(huf)+ avg(jpy)+ avg(lvl)+ avg(ltl)+ avg(nzd)+ avg (nok)+ avg(pln)+ avg(rub)+ avg(zar)+ avg(sek)+avg(chf)+ avg(try)+ avg(usd) Total 
	    from (select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end AUD, 
			   case when FOREIGN_CURRENCY_NUMBER = 124 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3
             ) group by cube (dcc_prov_settlbatch_no,merchant_id)					 
order by 1,2,3;
	  myReport dccReport%ROWTYPE;
row number :=1;
TB constant varchar2(2):=','; -- equal to TAB
begin
     open dccReport;
     fetch dccReport into myReport;
	 --dbms_output.put_line('Dept'||TB||'Emp'||TB||rpad('Employee',15,' ')||TB||' Salary');
     --dbms_output.put_line('No.'||TB||'No.'||TB||rpad('Name',15,' '));
     --dbms_output.put_line(rpad('-',42,'-'));
     dbms_output.put_line('Merchant' ||TB);
	 dbms_output.put_line('Settlement Date'||TB||' Merchant ID'||TB||'         Class    '||TB||' AUD '||TB||' CAD '||TB||' CZX '||TB||
	                     ' DKK '||TB||' GBP '||TB||' ISK '||TB||' HUF '||TB||' JPY '||TB||' LVL '||TB||' LTL '||TB||' NSD '||TB||' NOK '||TB||
						 ' PLN '||TB||' RUB '||TB||' ZAR '||TB||' SEK '||TB||' CHF '||TB||
						  ' TRY '||TB||' USD '||TB||' Total      ');
	 dbms_output.put_line(rpad('-',300,'-'));
	 	 loop
	     if dccReport%FOUND then
           dbms_output.put_line(myReport."Merchant Settlement Date" ||TB||rpad(nvl(myReport.MERCHANT_ID,' '), 35 ,' ')||TB||myReport.ITEM||TB||
		                        myReport.AUD||TB||myReport.CAD||TB||myReport.CZK||TB||myReport.DKK||TB||myReport.GBP||TB||myReport.HUF||TB||
								myReport.JPY||TB||myReport.LVL||TB||myReport.LTL||TB||myReport.NZD||TB||myReport.NOK||TB||myReport.PLN||TB||
								myReport.RUB||TB||myReport.ZAR||TB||myReport.SEK||TB||myReport.CHF||TB||myReport.TRY||TB||myReport.USD||TB||
								myReport.TOTAL);
           fetch dccReport into myReport;
           row := row + 1;
         elsif dccReport%NOTFOUND then
          exit;
         end if;
       end loop;
       if dccReport%ISOPEN then
         close dccReport;
       end if;
end;

Open in new window


ERROR at line 4:
ORA-06550: line 4, column 4:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:
( select <a SQL statement>
ORA-06550: line 63, column 56:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current exists max min prio
ORA-06550: line 63, column 128:
PLS-00103: Encountered the symbol "/" when expecting one of the following:
) intersect minus union
The symbol "intersect was inserted before "/" to continue.
ORA-06550: line 63, column 161:
PLS-00103: Encountered the symbol "*" when expecting one of the following:
) intersect minus order union
ORA-06550: line 63, column 169:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
. ( ) , * % & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multise
ORA-06550:
SQL> desc testreport
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 Merchant Settlement Date                           VARCHAR2(9)
 MERCHANT_ID                                        VARCHAR2(15)
 ITEM                                               CHAR(40)
 AUD                                                NUMBER(12)
 CAD                                                NUMBER(12)
 CZK                                                NUMBER(12)
 DKK                                                NUMBER(12)
 GBP                                                NUMBER(12)
 HUF                                                NUMBER(12)
 JPY                                                NUMBER(12)
 LVL                                                NUMBER(12)
 LTL                                                NUMBER(12)
 NZD                                                NUMBER(12)
 NOK                                                NUMBER(12)
 PLN                                                NUMBER(12)
 RUB                                                NUMBER(12)
 ZAR                                                NUMBER(12)
 SEK                                                NUMBER(12)
 CHF                                                NUMBER(12)
 TRY                                                NUMBER(12)
 USD                                                NUMBER(12)
 TOTAL                                              NUMBER(12)

Open in new window


Now this is what I'm getting  errors.
0
 
slightwv (䄆 Netminder) Commented:
>> CURSOR dccReport IS  ...  insert into testreport

No.  This is not what I posted.

Forget the Cursor.  Just a plain insert into select inside the pl/sql block.

That or loop through the cursor, fetch a row then insert that row.
      
I can't test this to see if the syntax/columns/etc... are correct but it should go something like:
begin
	  --select "Merchant Settlement Date",MERCHANT_ID,ITEM,AUD,CAD,CZK,DKK,GBP,HUF,JPY,LVL,LTL,NZD,NOK,PLN,RUB,ZAR,SEK,CHF,TRY,USD,TOTAL from testreport;
	  insert into testreport 
	  ("Merchant Settlement Date",MERCHANT_ID,ITEM,AUD,CAD,CZK,DKK,GBP,HUF,JPY,LVL,LTL,NZD,NOK,PLN,RUB,ZAR,SEK,CHF,TRY,USD,TOTAL)
						 select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') "Merchant Settlement Date", merchant_id,'1-DCC value' item,  
						 sum(aud) aud, sum(cad) cad,sum(czk) czk,sum(dkk) dkk, sum(gbp) gbp,sum(huf) huf,sum(jpy) jpy,sum(lvl) lvl,sum(ltl) ltl,sum(nzd) nzd,sum(nok) nok,sum(pln) pln,sum(rub) rub,sum(zar) zar,sum(sek) sek,sum(chf) chf,sum(try) try,sum(usd) usd,
						 sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
						 from (		 select dcc_prov_settlbatch_no, merchant_id,
		       				  		 case when FOREIGN_CURRENCY_NUMBER = 36  then FOREIGN_CURRENCY_AMOUNT else 0 end AUD,
									 case when FOREIGN_CURRENCY_NUMBER = 124 then FOREIGN_CURRENCY_AMOUNT else 0 end CAD,
			     					 case when FOREIGN_CURRENCY_NUMBER = 203 then FOREIGN_CURRENCY_AMOUNT else 0 end CZK,
			                         case when FOREIGN_CURRENCY_NUMBER = 208 then FOREIGN_CURRENCY_AMOUNT else 0 end DKK,
			   						 case when FOREIGN_CURRENCY_NUMBER = 826 then FOREIGN_CURRENCY_AMOUNT else 0 end GBP,
			   						 case when FOREIGN_CURRENCY_NUMBER = 348 then FOREIGN_CURRENCY_AMOUNT else 0 end HUF,
			   						 case when FOREIGN_CURRENCY_NUMBER = 392 then FOREIGN_CURRENCY_AMOUNT else 0 end JPY,
									 case when FOREIGN_CURRENCY_NUMBER = 428 then FOREIGN_CURRENCY_AMOUNT else 0 end LVL,
			   						 case when FOREIGN_CURRENCY_NUMBER = 440 then FOREIGN_CURRENCY_AMOUNT else 0 end LTL,
			   						 case when FOREIGN_CURRENCY_NUMBER = 554 then FOREIGN_CURRENCY_AMOUNT else 0 end NZD,
			   						 case when FOREIGN_CURRENCY_NUMBER = 578 then FOREIGN_CURRENCY_AMOUNT else 0 end NOK,
			   						 case when FOREIGN_CURRENCY_NUMBER = 985 then FOREIGN_CURRENCY_AMOUNT else 0 end PLN,
			   						 case when FOREIGN_CURRENCY_NUMBER = 643 then FOREIGN_CURRENCY_AMOUNT else 0 end RUB,
			   						 case when FOREIGN_CURRENCY_NUMBER = 710 then FOREIGN_CURRENCY_AMOUNT else 0 end ZAR,
			   						 case when FOREIGN_CURRENCY_NUMBER = 752 then FOREIGN_CURRENCY_AMOUNT else 0 end SEK,
			   						 case when FOREIGN_CURRENCY_NUMBER = 756 then FOREIGN_CURRENCY_AMOUNT else 0 end CHF,
			   						 case when FOREIGN_CURRENCY_NUMBER = 949 then FOREIGN_CURRENCY_AMOUNT else 0 end TRY,
			   						 case when FOREIGN_CURRENCY_NUMBER = 840 then FOREIGN_CURRENCY_AMOUNT else 0 end USD
			   						 from dcct_dtf
									 --where term_settl_batch_no=120530   
			                         where dcc_process_status=3) 
     	                             group by (dcc_prov_settlbatch_no,merchant_id)
                         union	   
	                     select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') Merchant_Date, merchant_id,'2-Original Sale Value EURO' item,  
                         sum(aud) aud, sum(cad) cad,sum(czk),sum(dkk), sum(gbp),sum(huf),sum(jpy),sum(lvl),sum(ltl),sum(nzd),sum(nok),sum(pln),sum(rub),sum(zar),sum(sek),sum(chf),sum(try),sum(usd),
                         sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
                         from ( 	 select dcc_prov_settlbatch_no, merchant_id,
	       				 	  		 case when FOREIGN_CURRENCY_NUMBER = 36  then BASE_CURRENCY_AMOUNT else 0 end AUD,
									 case when FOREIGN_CURRENCY_NUMBER = 124 then BASE_CURRENCY_AMOUNT else 0 end CAD,
 									 case when FOREIGN_CURRENCY_NUMBER = 203 then BASE_CURRENCY_AMOUNT else 0 end CZK,
 									 case when FOREIGN_CURRENCY_NUMBER = 208 then BASE_CURRENCY_AMOUNT else 0 end DKK,
									 case when FOREIGN_CURRENCY_NUMBER = 826 then BASE_CURRENCY_AMOUNT else 0 end GBP,
									 case when FOREIGN_CURRENCY_NUMBER = 348 then BASE_CURRENCY_AMOUNT else 0 end HUF,
									 case when FOREIGN_CURRENCY_NUMBER = 392 then BASE_CURRENCY_AMOUNT else 0 end JPY,
									 case when FOREIGN_CURRENCY_NUMBER = 428 then BASE_CURRENCY_AMOUNT else 0 end LVL,
									 case when FOREIGN_CURRENCY_NUMBER = 440 then BASE_CURRENCY_AMOUNT else 0 end LTL,
									 case when FOREIGN_CURRENCY_NUMBER = 554 then BASE_CURRENCY_AMOUNT else 0 end NZD,
									 case when FOREIGN_CURRENCY_NUMBER = 578 then BASE_CURRENCY_AMOUNT else 0 end NOK,
									 case when FOREIGN_CURRENCY_NUMBER = 985 then BASE_CURRENCY_AMOUNT else 0 end PLN,
									 case when FOREIGN_CURRENCY_NUMBER = 643 then BASE_CURRENCY_AMOUNT else 0 end RUB,
									 case when FOREIGN_CURRENCY_NUMBER = 710 then BASE_CURRENCY_AMOUNT else 0 end ZAR,
									 case when FOREIGN_CURRENCY_NUMBER = 752 then BASE_CURRENCY_AMOUNT else 0 end SEK,
									 case when FOREIGN_CURRENCY_NUMBER = 756 then BASE_CURRENCY_AMOUNT else 0 end CHF,
									 case when FOREIGN_CURRENCY_NUMBER = 949 then BASE_CURRENCY_AMOUNT else 0 end TRY,
									 case when FOREIGN_CURRENCY_NUMBER = 840 then BASE_CURRENCY_AMOUNT else 0 end USD
									 from dcct_dtf
		   --where term_settl_batch_no=120530   
		   		   		where dcc_process_status=3
     					) group by (dcc_prov_settlbatch_no,merchant_id)	 			 		 
						union 
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') MerchantDate, merchant_id,'3-DCC opt-in performance' item, avg(aud) aus, avg(cad) cad, avg(czk) czk, avg(dkk) dkk, avg(gbp) gbp, avg(huf) huf, avg(jpy) jpy, avg(lvl) lvl, avg(ltl) ltl, avg(nzd) nzd, avg(nok) nok, avg(pln) pln, avg(rub) rub, avg(zar) zar, avg(sek) sek,avg(chf) chf, avg(try) try, avg(usd) usd, 
avg(aud)+avg(cad)+avg(czk)+avg(dkk)+ avg(gbp)+avg(huf)+ avg(jpy)+ avg(lvl)+ avg(ltl)+ avg(nzd)+ avg (nok)+ avg(pln)+ avg(rub)+ avg(zar)+ avg(sek)+avg(chf)+ avg(try)+ avg(usd) Total 
	    from ( select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end AUD, 
			   case when FOREIGN_CURRENCY_NUMBER = 124 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3
             ) group by (dcc_prov_settlbatch_no,merchant_id)					 
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') "Merchant Settlement Date", merchant_id,'1-DCC value' item,  
	sum(aud) aud, sum(cad) cad,sum(czk) czk,sum(dkk) dkk, sum(gbp) gbp,sum(huf) huf,sum(jpy) jpy,sum(lvl) lvl,sum(ltl) ltl,sum(nzd) nzd,sum(nok) nok,sum(pln) pln,sum(rub) rub,sum(zar) zar,sum(sek) sek,sum(chf) chf,sum(try) try,sum(usd) usd,
	sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
	from (select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then FOREIGN_CURRENCY_AMOUNT else 0 end AUD,
			   case when FOREIGN_CURRENCY_NUMBER = 124 then FOREIGN_CURRENCY_AMOUNT else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then FOREIGN_CURRENCY_AMOUNT else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then FOREIGN_CURRENCY_AMOUNT else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then FOREIGN_CURRENCY_AMOUNT else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then FOREIGN_CURRENCY_AMOUNT else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then FOREIGN_CURRENCY_AMOUNT else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then FOREIGN_CURRENCY_AMOUNT else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then FOREIGN_CURRENCY_AMOUNT else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then FOREIGN_CURRENCY_AMOUNT else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then FOREIGN_CURRENCY_AMOUNT else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then FOREIGN_CURRENCY_AMOUNT else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then FOREIGN_CURRENCY_AMOUNT else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then FOREIGN_CURRENCY_AMOUNT else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then FOREIGN_CURRENCY_AMOUNT else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then FOREIGN_CURRENCY_AMOUNT else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then FOREIGN_CURRENCY_AMOUNT else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then FOREIGN_CURRENCY_AMOUNT else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3
     	     ) group by cube (dcc_prov_settlbatch_no,merchant_id)
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') Merchant_Date, merchant_id,'2-Original Sale Value EURO' item,  
sum(aud) aud, sum(cad) cad,sum(czk),sum(dkk), sum(gbp),sum(huf),sum(jpy),sum(lvl),sum(ltl),sum(nzd),sum(nok),sum(pln),sum(rub),sum(zar),sum(sek),sum(chf),sum(try),sum(usd),
sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
from (select dcc_prov_settlbatch_no, merchant_id,
	       case when FOREIGN_CURRENCY_NUMBER = 36  then BASE_CURRENCY_AMOUNT else 0 end AUD,
		   case when FOREIGN_CURRENCY_NUMBER = 124 then BASE_CURRENCY_AMOUNT else 0 end CAD,
		   case when FOREIGN_CURRENCY_NUMBER = 203 then BASE_CURRENCY_AMOUNT else 0 end CZK,
		   case when FOREIGN_CURRENCY_NUMBER = 208 then BASE_CURRENCY_AMOUNT else 0 end DKK,
		   case when FOREIGN_CURRENCY_NUMBER = 826 then BASE_CURRENCY_AMOUNT else 0 end GBP,
		   case when FOREIGN_CURRENCY_NUMBER = 348 then BASE_CURRENCY_AMOUNT else 0 end HUF,
		   case when FOREIGN_CURRENCY_NUMBER = 392 then BASE_CURRENCY_AMOUNT else 0 end JPY,
		   case when FOREIGN_CURRENCY_NUMBER = 428 then BASE_CURRENCY_AMOUNT else 0 end LVL,
		   case when FOREIGN_CURRENCY_NUMBER = 440 then BASE_CURRENCY_AMOUNT else 0 end LTL,
		   case when FOREIGN_CURRENCY_NUMBER = 554 then BASE_CURRENCY_AMOUNT else 0 end NZD,
		   case when FOREIGN_CURRENCY_NUMBER = 578 then BASE_CURRENCY_AMOUNT else 0 end NOK,
		   case when FOREIGN_CURRENCY_NUMBER = 985 then BASE_CURRENCY_AMOUNT else 0 end PLN,
		   case when FOREIGN_CURRENCY_NUMBER = 643 then BASE_CURRENCY_AMOUNT else 0 end RUB,
		   case when FOREIGN_CURRENCY_NUMBER = 710 then BASE_CURRENCY_AMOUNT else 0 end ZAR,
		   case when FOREIGN_CURRENCY_NUMBER = 752 then BASE_CURRENCY_AMOUNT else 0 end SEK,
		   case when FOREIGN_CURRENCY_NUMBER = 756 then BASE_CURRENCY_AMOUNT else 0 end CHF,
		   case when FOREIGN_CURRENCY_NUMBER = 949 then BASE_CURRENCY_AMOUNT else 0 end TRY,
		   case when FOREIGN_CURRENCY_NUMBER = 840 then BASE_CURRENCY_AMOUNT else 0 end USD
		   from dcct_dtf
		   --where term_settl_batch_no=120530   
		   where dcc_process_status=3
     ) group by cube(dcc_prov_settlbatch_no,merchant_id)
union
select distinct to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY') MerchantDate, merchant_id,'3-DCC opt-in performance' item, avg(aud) aus, avg(cad) cad, avg(czk) czk, avg(dkk) dkk, avg(gbp) gbp, avg(huf) huf, avg(jpy) jpy, avg(lvl) lvl, avg(ltl) ltl, avg(nzd) nzd, avg(nok) nok, avg(pln) pln, avg(rub) rub, avg(zar) zar, avg(sek) sek,avg(chf) chf, avg(try) try, avg(usd) usd, 
avg(aud)+avg(cad)+avg(czk)+avg(dkk)+ avg(gbp)+avg(huf)+ avg(jpy)+ avg(lvl)+ avg(ltl)+ avg(nzd)+ avg (nok)+ avg(pln)+ avg(rub)+ avg(zar)+ avg(sek)+avg(chf)+ avg(try)+ avg(usd) Total 
	    from (select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end AUD, 
			   case when FOREIGN_CURRENCY_NUMBER = 124 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3
             ) group by cube (dcc_prov_settlbatch_no,merchant_id)					 
order by 1,2,3;
end;
/

Open in new window

0
 
adnankhAuthor Commented:
Dear Expert!,

Thanks for your advise, as i new to this pl/sql programming so please bear with me.
Now I've done as you mentioned above

1- PL/SQL Block which insert data into testreport from Master Table.
2- Cursor that fetch data and displying records row by row.

But I 've another problem now

I've been asked that this report that cursor generating should be CSV format file and store in some location in server.
This report break down by Date, Merchant ID and gives daily total  and Monthly total (Please see the attachement)
I've atatched the sample report just to you understand as well.

So my question is how I can generate that exact format (as attached in sample file and sent it to some location in csv style)?

Please advise.

Regards
DCC-Report.xlsx
0
 
slightwv (䄆 Netminder) Commented:
So you are only inserting into the testreport table so you can generate the report?

Will ALL the rows in testreport be used fo rthe report?

Writing to a file on the database server can be done using UTL_FILE.

The XLS file you uploaded is pretty detailed and creating that in a CSV format form PL/SQL will require some pretty complex code to get the column breaks and summary rows.  

Honestly it is more complex than can probably be answered on a Q&A site like this.

Does this have to be done with PL/SQL?  sqlplus has some pretty poweful reporting capability that will probably get you closer but I'm not sure if it can generate that exact report.  You will just need to mess around with it to see how close you can get with it.

If sqlplus is an option let us know and we can probably get you started with its reporting features.
0
 
adnankhAuthor Commented:
Thank you for your comment & questions

So you are only inserting into the testreport table so you can generate the report? Yes

Will ALL the rows in testreport be used fo rthe report?Yes

Does this have to be done with PL/SQL?  Well the objective is to get this report on report server to get pickup every day say 8:30AM. So as a expert what would you do then?
My understanding or model is
 
Step1- Create a testreport table , and extract YESTERDAY's data in it using PL/SQL block (as you mentioned before) from Master Table using date input(which I dont know yet in pl/sql block). keep in mind that both table in same schema , we cann't use Master table due to restrictions.
 
Step2- write a cursor to generate output as you saw in xls file attachment and put into report server as a csv format.
 
If sqlplus is an option let us know and we can probably get you started with its reporting features. Yes Please (I'm able to break on Merchant ID, DATE) and use CUBE. You can see my sql. But how I can automate step1,step2 within just simple sqlplus? this is something I can't figure out...


YOur co-operation much appreciated  
Rgds,
0
 
slightwv (䄆 Netminder) Commented:
>>we cann't use Master table due to restrictions.

You are using the master table to retrieve the rows for the report table.  I'm not following how selecting from the table directly to generate the report is any different, but if you must then you can use the insert statement above.

Now it is only the actual generation of the report.

sqlplus reporting is talked about in the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_six.htm

You need to set up column breaks to remove the repeating columns like merchant id and setlement date.

You will need to use COMPUTE to get the running totals.
0
 
adnankhAuthor Commented:
Thank you , I've got almost there but I'm not using COMPUTE, I'm using CUBE instead. Please check SQL below ..
break on "Merchant ID" skip 1 on  "Merchant Settlement Date" 
select distinct lpad(nvl(to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY'),'Monthly Total'), 15 ,' ') "Merchant Settlement Date", lpad(nvl(merchant_id,'Daily total of all Merchant') , 30 ,' ')"Merchant ID",'1-DCC value' item,  
	sum(aud) aud, sum(cad) cad,sum(czk) czk,sum(dkk) dkk, sum(gbp) gbp,sum(huf) huf,sum(jpy) jpy,sum(lvl) lvl,sum(ltl) ltl,sum(nzd) nzd,sum(nok) nok,sum(pln) pln,sum(rub) rub,sum(zar) zar,sum(sek) sek,sum(chf) chf,sum(try) try,sum(usd) usd,
	sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
	from (
      		select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then FOREIGN_CURRENCY_AMOUNT else 0 end AUD,
			   case when FOREIGN_CURRENCY_NUMBER = 124 then FOREIGN_CURRENCY_AMOUNT else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then FOREIGN_CURRENCY_AMOUNT else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then FOREIGN_CURRENCY_AMOUNT else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then FOREIGN_CURRENCY_AMOUNT else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then FOREIGN_CURRENCY_AMOUNT else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then FOREIGN_CURRENCY_AMOUNT else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then FOREIGN_CURRENCY_AMOUNT else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then FOREIGN_CURRENCY_AMOUNT else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then FOREIGN_CURRENCY_AMOUNT else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then FOREIGN_CURRENCY_AMOUNT else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then FOREIGN_CURRENCY_AMOUNT else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then FOREIGN_CURRENCY_AMOUNT else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then FOREIGN_CURRENCY_AMOUNT else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then FOREIGN_CURRENCY_AMOUNT else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then FOREIGN_CURRENCY_AMOUNT else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then FOREIGN_CURRENCY_AMOUNT else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then FOREIGN_CURRENCY_AMOUNT else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3 
     	     ) group by cube (dcc_prov_settlbatch_no,merchant_id)
union	   
	select distinct lpad(nvl(to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY'),'Monthly Total'), 15 ,' ') "Merchant Settlement Date", lpad(nvl(merchant_id,'Daily total of all Merchant') , 30 ,' ')"Merchant ID",'2-Original Sale Value EURO' item,  
sum(aud) aud, sum(cad) cad,sum(czk),sum(dkk), sum(gbp),sum(huf),sum(jpy),sum(lvl),sum(ltl),sum(nzd),sum(nok),sum(pln),sum(rub),sum(zar),sum(sek),sum(chf),sum(try),sum(usd),
sum(aud)+sum(cad)+sum(czk)+sum(dkk)+ sum(gbp)+sum(huf)+sum(jpy)+sum(lvl)+sum(ltl)+sum(nzd)+sum(nok)+sum(pln)+sum(rub)+sum(zar)+sum(sek)+sum(chf)+sum(try)+sum(usd) Total
from (
       select dcc_prov_settlbatch_no, merchant_id,
	       case when FOREIGN_CURRENCY_NUMBER = 36  then BASE_CURRENCY_AMOUNT else 0 end AUD,
		   case when FOREIGN_CURRENCY_NUMBER = 124 then BASE_CURRENCY_AMOUNT else 0 end CAD,
		   case when FOREIGN_CURRENCY_NUMBER = 203 then BASE_CURRENCY_AMOUNT else 0 end CZK,
		   case when FOREIGN_CURRENCY_NUMBER = 208 then BASE_CURRENCY_AMOUNT else 0 end DKK,
		   case when FOREIGN_CURRENCY_NUMBER = 826 then BASE_CURRENCY_AMOUNT else 0 end GBP,
		   case when FOREIGN_CURRENCY_NUMBER = 348 then BASE_CURRENCY_AMOUNT else 0 end HUF,
		   case when FOREIGN_CURRENCY_NUMBER = 392 then BASE_CURRENCY_AMOUNT else 0 end JPY,
		   case when FOREIGN_CURRENCY_NUMBER = 428 then BASE_CURRENCY_AMOUNT else 0 end LVL,
		   case when FOREIGN_CURRENCY_NUMBER = 440 then BASE_CURRENCY_AMOUNT else 0 end LTL,
		   case when FOREIGN_CURRENCY_NUMBER = 554 then BASE_CURRENCY_AMOUNT else 0 end NZD,
		   case when FOREIGN_CURRENCY_NUMBER = 578 then BASE_CURRENCY_AMOUNT else 0 end NOK,
		   case when FOREIGN_CURRENCY_NUMBER = 985 then BASE_CURRENCY_AMOUNT else 0 end PLN,
		   case when FOREIGN_CURRENCY_NUMBER = 643 then BASE_CURRENCY_AMOUNT else 0 end RUB,
		   case when FOREIGN_CURRENCY_NUMBER = 710 then BASE_CURRENCY_AMOUNT else 0 end ZAR,
		   case when FOREIGN_CURRENCY_NUMBER = 752 then BASE_CURRENCY_AMOUNT else 0 end SEK,
		   case when FOREIGN_CURRENCY_NUMBER = 756 then BASE_CURRENCY_AMOUNT else 0 end CHF,
		   case when FOREIGN_CURRENCY_NUMBER = 949 then BASE_CURRENCY_AMOUNT else 0 end TRY,
		   case when FOREIGN_CURRENCY_NUMBER = 840 then BASE_CURRENCY_AMOUNT else 0 end USD
		   from dcct_dtf
		   --where term_settl_batch_no=120530   
		   where dcc_process_status=3
     ) group by cube (dcc_prov_settlbatch_no,merchant_id)	 			 		 
union 
select distinct lpad(nvl(to_char(to_date(substr(dcc_prov_settlbatch_no,1,6),'YYMMDD'),'DD-Mon-YY'),'Monthly Total'), 15 ,' ') "Merchant Settlement Date", lpad(nvl(merchant_id,'Daily total of all Merchant') , 30 ,' ')"Merchant ID",'3-DCC opt-in performance' item, avg(aud) aus, avg(cad) cad, avg(czk) czk, avg(dkk) dkk, avg(gbp) gbp, avg(huf) huf, avg(jpy) jpy, avg(lvl) lvl, avg(ltl) ltl, avg(nzd) nzd, avg(nok) nok, avg(pln) pln, avg(rub) rub, avg(zar) zar, avg(sek) sek,avg(chf) chf, avg(try) try, avg(usd) usd, 
avg(aud)+avg(cad)+avg(czk)+avg(dkk)+ avg(gbp)+avg(huf)+ avg(jpy)+ avg(lvl)+ avg(ltl)+ avg(nzd)+ avg (nok)+ avg(pln)+ avg(rub)+ avg(zar)+ avg(sek)+avg(chf)+ avg(try)+ avg(usd) Total 
	    from (
               select dcc_prov_settlbatch_no, merchant_id,
		       case when FOREIGN_CURRENCY_NUMBER = 36  then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end AUD, 
			   case when FOREIGN_CURRENCY_NUMBER = 124 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CAD,
			   case when FOREIGN_CURRENCY_NUMBER = 203 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CZK,
			   case when FOREIGN_CURRENCY_NUMBER = 208 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end DKK,
			   case when FOREIGN_CURRENCY_NUMBER = 826 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end GBP,
			   case when FOREIGN_CURRENCY_NUMBER = 348 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end HUF,
			   case when FOREIGN_CURRENCY_NUMBER = 392 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end JPY,
			   case when FOREIGN_CURRENCY_NUMBER = 428 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LVL,
			   case when FOREIGN_CURRENCY_NUMBER = 440 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end LTL,
			   case when FOREIGN_CURRENCY_NUMBER = 554 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NZD,
			   case when FOREIGN_CURRENCY_NUMBER = 578 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end NOK,
			   case when FOREIGN_CURRENCY_NUMBER = 985 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end PLN,
			   case when FOREIGN_CURRENCY_NUMBER = 643 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end RUB,
			   case when FOREIGN_CURRENCY_NUMBER = 710 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end ZAR,
			   case when FOREIGN_CURRENCY_NUMBER = 752 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end SEK,
			   case when FOREIGN_CURRENCY_NUMBER = 756 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end CHF,
			   case when FOREIGN_CURRENCY_NUMBER = 949 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end TRY,
			   case when FOREIGN_CURRENCY_NUMBER = 840 then (select trunc(((select count(*) from dcct_dtf where dcc_process_status=3)/(select count(*) from dcct_dtf))*100,0) from dcct_dtf group by 1 having count(*) > 1) else 0 end USD
			   from dcct_dtf
			   --where term_settl_batch_no=120530   
			   where dcc_process_status=3
             ) group by cube (dcc_prov_settlbatch_no,merchant_id) 
order by 1,2,3
/

Open in new window

and output of this query is attached.. Can you please have a look into it. Last line showing
" Daily total of all Merchant" it should be "Monthly total of all Merchant" and few formatting I still can't figure out...The NUMBERS are good and looks fine.
Now how to print exact same output in csv format? If I spool myreport.csv it's put all heading /records in one column in excel, I use ||','|| but not much helping. Any other idea Please? and How I can run this mix of sqlplus commands and sql commands in pl/sql block, So we can call that Procedure and print this ? Any Idea

Kind Regards,
DCC-ReportV1.0.xlsx
0
 
slightwv (䄆 Netminder) Commented:
>>If I spool myreport.csv it's put all heading /records in one column in excel, I use ||','|| but not much helping

You cannot mix sqplus and PL/SQL commands inside a stored procedure.  Since you mention spool and procedure, I'm not sure what you are doing.  Are you using dbms_output in the procedure then using sqlplus to execute the procedure?

>>put all heading /records in one column in excel, I use ||','|| but not much helping

For the column heading I normally just add a select from dual as part of the union with a sortOrder column.

Something like:

select col1, col2 from (
select 1 sortOrder, 'header1' col1, 'header2' col2 from dual
union all
select 2,'z','x' from dual
union all
select 3, 'a','b' from dual
order by 1
)
/

>>" Daily total of all Merchant"

I'll see if I can take a better look at this later today.  If you need more immeidate assistance please click the Request attention link above and a Moderator will see if they can locate additional Experts to help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now