Home » SQL & PL/SQL » SQL & PL/SQL » CSV report
CSV report [message #543968] Sat, 18 February 2012 08:54 Go to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Hi all,

I have a requirement to generate a report in csv file, please find attached scripts, sql query and actual output and expected output.

In the query, I am hard-coding month&year, when I run in march 2012 it should include march 2012 in output (similarly for every month going forward), to do that I have to modify my query to include march 2012 & preceding months in every individual query, this report needs to be generated once every month.

I am looking for a generic solution which does not need to be modified every month, also in the output,under "Mail File" data should be in ascending order, in the "Total Mailed" all the months should have grand total.

oracle version : 10.2.0.1.0

Thanks
Raghu

  • Attachment: data.txt
    (Size: 18.89KB, Downloaded 92 times)
Re: CSV report [message #543970 is a reply to message #543968] Sat, 18 February 2012 08:59 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Please find actual output as attachment.
Re: CSV report [message #543972 is a reply to message #543970] Sat, 18 February 2012 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Rememeber: Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

Regards
Michel
Re: CSV report [message #543973 is a reply to message #543972] Sat, 18 February 2012 09:29 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Rules
*********

1) All the extract id's in zam_co_extract_log should be included in the output, irrespective of it has sale or not.

2) Join condition between plc_sr_au_drrs_hist and zam_sr_au_incentive_cust_hist is addr1 field and other conditions mentioned below.

3) I need to provide count of sales in each extract id of zam_co_extract_log by month wise (that's why it is grouped by
GROUP BY extract_id,added_date, applied_mon)

For ex:- for 09-dec-2011 file,3 sales in jan 2012 and 1 sale in feb 2012, grand total is 4 and percent of sale is (4/896)*100 (0.45)

Similar in the "Total Mailed" last row, it should have grand total of each month.

Please find create table, insert statements and sql used to generate the output.


create & insert scripts
*********************************

create table zam_co_extract_log
(extract_id number,added_date date,record_cnt number);

begin
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (2, TO_DATE('12/08/2011 08:02:38', 'MM/DD/YYYY HH24:MI:SS'), 144);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (21, TO_DATE('12/09/2011 09:13:25', 'MM/DD/YYYY HH24:MI:SS'), 896);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (42, TO_DATE('12/13/2011 03:26:36', 'MM/DD/YYYY HH24:MI:SS'), 2188);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (61, TO_DATE('12/15/2011 04:33:02', 'MM/DD/YYYY HH24:MI:SS'), 624);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (81, TO_DATE('12/21/2011 08:39:22', 'MM/DD/YYYY HH24:MI:SS'), 1200);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (102, TO_DATE('12/28/2011 07:09:30', 'MM/DD/YYYY HH24:MI:SS'), 1486);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (121, TO_DATE('01/04/2012 02:41:16', 'MM/DD/YYYY HH24:MI:SS'), 923);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (141, TO_DATE('01/11/2012 12:39:45', 'MM/DD/YYYY HH24:MI:SS'), 1076);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (161, TO_DATE('01/18/2012 04:25:44', 'MM/DD/YYYY HH24:MI:SS'), 1222);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (181, TO_DATE('01/25/2012 01:10:32', 'MM/DD/YYYY HH24:MI:SS'), 1667);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (201, TO_DATE('02/01/2012 01:43:54', 'MM/DD/YYYY HH24:MI:SS'), 1858);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (221, TO_DATE('02/08/2012 04:29:54', 'MM/DD/YYYY HH24:MI:SS'), 2038);
Insert into zam_CO_EXTRACT_LOG
   (EXTRACT_ID, ADDED_DATE, RECORD_CNT)
 Values
   (241, TO_DATE('02/15/2012 02:30:00', 'MM/DD/YYYY HH24:MI:SS'), 2527);
COMMIT;
end;

/


create table zam_sr_au_incentive_cust_hist
(extract_id number,addr1 varchar2(50), added_date date);

begin
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (2, '14824 SW 125TH CT', TO_DATE('12/09/2011 04:51:12', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (21, 'PO BOX 324', TO_DATE('12/09/2011 09:52:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (21, '3920 14TH AVE SE', TO_DATE('12/09/2011 09:52:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (21, '1555 CRAYTON RD', TO_DATE('12/09/2011 09:52:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (21, '306 N MAIN ST', TO_DATE('12/09/2011 09:52:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (21, '15501 BRUCE B DOWNS BLVD', TO_DATE('12/09/2011 09:52:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (42, 'PO BOX 712', TO_DATE('12/14/2011 04:55:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (42, '38 CAYUGA RD', TO_DATE('12/14/2011 04:55:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (42, '14824 SW 125TH CT', TO_DATE('12/14/2011 04:55:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (42, '815 W BOYNTON BEACH BLVD', TO_DATE('12/14/2011 04:55:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (42, '4722 SW 13TH CT', TO_DATE('12/14/2011 04:55:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (61, '9100 VANCE ST', TO_DATE('12/15/2011 04:34:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (61, '4641 POND RIDGE DR', TO_DATE('12/15/2011 04:34:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (61, '324 MAIN ST', TO_DATE('12/15/2011 04:34:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (61, '12780 SW 117TH ST', TO_DATE('12/15/2011 04:34:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (61, '332 MEADOWOOD LN', TO_DATE('12/15/2011 04:34:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (61, '807 W OAKLAND PARK BLVD', TO_DATE('12/15/2011 04:34:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, 'PO BOX 324', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, '1850 COTILLION DR', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, '500 TRINITY LN N', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, '411 WALNUT ST', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, '3410 GALT OCEAN DR', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, '1020 8TH AVE S', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, '4501 WOODWARD AVE', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (81, '3300 PORT ROYALE DR N', TO_DATE('12/21/2011 08:53:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (102, '2111 KEENAN AVE', TO_DATE('12/28/2011 07:10:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (102, 'PO BOX 694', TO_DATE('12/28/2011 07:10:06', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (121, '469 ENA RD', TO_DATE('01/04/2012 02:41:25', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (121, '4501 WOODWARD AVE', TO_DATE('01/04/2012 02:41:26', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (121, '21 CARDINAL AVE', TO_DATE('01/04/2012 02:41:27', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (161, '1232 N FAIRVIEW AVE', TO_DATE('01/18/2012 04:26:42', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (161, '13613 LAKES WAY', TO_DATE('01/18/2012 04:26:41', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (181, 'PO BOX 1233', TO_DATE('01/25/2012 01:34:08', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (181, 'PO BOX 5724', TO_DATE('01/25/2012 01:34:10', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (181, 'PO BOX 161', TO_DATE('01/25/2012 01:34:10', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (201, '212 LOCH LOW DR', TO_DATE('02/01/2012 01:56:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (221, '1580 NW 128TH DR', TO_DATE('02/08/2012 04:30:08', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (221, '251 10TH ST NW', TO_DATE('02/08/2012 04:30:08', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (221, 'PO BOX 31', TO_DATE('02/08/2012 04:30:09', 'MM/DD/YYYY HH24:MI:SS'));
Insert into zam_SR_AU_INCENTIVE_CUST_HIST
   (EXTRACT_ID, ADDR1, ADDED_DATE)
 Values
   (221, '11839 WHITEHILL ST', TO_DATE('02/08/2012 04:30:10', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
end;

/


create table  plc_sr_au_drrs_hist (applied_date date,addr1 varchar2(50), 
                                  reject_code varchar2(10), new_used_ind varchar2(1));

begin
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('12/15/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '4722 SW 13th Ct', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('12/15/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PO Box 712', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('12/15/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '815 W Boynton Beach Blvd', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '306 N Main St', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '15501 Bruce B Downs Blvd', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '324 Main St', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '1555 Crayton Rd', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '469 Ena Rd', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '500 Trinity Ln N', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '332 Meadowood Ln', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '2111 Keenan Ave', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '3300 Port Royale Dr N', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PO Box 694', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '411 Walnut St', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '324 Main St', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '12780 SW 117th St', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '38 Cayuga Rd', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('01/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '1850 Cotillion Dr', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '14824  125TH CT', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PO Box 161', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PO Box 1233', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '13613 Lakes Way', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '807 W Oakland Park Blvd', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PO Box 5724', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PO Box 31', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '21 Cardinal Ave', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '4501 Woodward Ave', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PO Box 324', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '3300 Port Royale Dr N', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '1580 NW 128th Dr', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '4641 Pond Ridge Dr', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '4641 Pond Ridge Dr', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '1020 8th Ave S', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '1232 N Fairview Ave', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '251 10th St NW', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '212 Loch Low Dr', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '3410 Galt Ocean Dr', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '3920 14TH AVE ', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '9100 Vance St', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '9100 Vance St', 'W');
Insert into plc_SR_AU_drrS_HIST
   (APPLIED_DATE, ADDR1, NEW_USED_IND)
 Values
   (TO_DATE('02/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '11839 Whitehill St', 'W');
COMMIT;
end;

/

Query
**********
set linesize 2000
set pages 5000
set space 0
set term off
set head off
set pagesize 0
set arraysize 1
set feedback off
set embedded on


spool c:\report.txt

SELECT 'Mail File'  ||','|| 
        'Count'     ||','|| 
       'Sales Dec 2011'  ||','||  
        'Sales Jan 2012' ||','|| 
       'Sales Feb 2012' ||','||       
        'Total Sales'   ||','|| 
       'Percent of Sale'
FROM DUAL
union all
SELECT to_char(TRUNC (m.added_date),'dd-Mon-yyyy') ||','||
       m.record_cnt ||','||
       nvl(b."Sales Dec 2011",0)  ||','|| 
       nvl(b."Sales Jan 2012",0) ||','||
      nvl(b."Sales Feb 2012",0)  ||','||       
       nvl(b."Total Sales",0)  ||','||
       nvl(b."sales_percent",0) 
FROM zam_co_extract_log m, 
(SELECT  tab1.extract_id,
         SUM (CASE WHEN tab1.applied_mon = 'DEC-2011' THEN cnt ELSE 0  END) "Sales Dec 2011",
         SUM (CASE WHEN tab1.applied_mon = 'JAN-2012' THEN cnt ELSE 0  END) "Sales Jan 2012",
         SUM (CASE WHEN tab1.applied_mon = 'FEB-2012' THEN cnt ELSE 0  END) "Sales Feb 2012",
         SUM (cnt) "Total Sales"  ,  
        round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent"
    FROM (SELECT  extract_id, added_date, applied_mon, COUNT (1) cnt
              FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,
                     TO_CHAR (drr.applied_date, 'MON') || '-' || TO_CHAR (drr.applied_date, 'YYYY') applied_mon
                      FROM zam_co_extract_log l,
                           plc_sr_au_drrs_hist drr,
                           zam_sr_au_incentive_cust_hist inc
                     WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1))
                       AND l.extract_id = inc.extract_id
                       AND drr.applied_date > inc.added_date                                             
                       AND drr.reject_code IS NULL
                   )
          GROUP BY extract_id,added_date, applied_mon) tab1,
         (SELECT TRUNC (added_date) added_date, record_cnt,extract_id FROM zam_co_extract_log) tab2
   WHERE tab1.added_date = tab2.added_date
GROUP BY tab1.extract_id,tab2.record_cnt) b
where m.extract_id = b.extract_id(+)
union all
select 'Total Mailed'||','|| rec_cnt||','|| null||','|| null||','||null||','|| cnt||','|| round(cnt/(rec_cnt)*100,2)
from
(
SELECT count(1) cnt
                      FROM zam_co_extract_log l,
                           plc_sr_au_drrs_hist drr,
                           zam_sr_au_incentive_cust_hist inc
                     WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1))
                       AND l.extract_id = inc.extract_id
                       AND drr.applied_date > inc.added_date  
                       AND drr.reject_code IS NULL),         
(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)
/

spool off
set feedback on



Actual output
**************
Mail File,Count,Sales Dec 2011,Sales Jan 2012,Sales Feb 2012,Total Sales,Percent of Sale                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
28-Dec-11,1486,0,2,0,2,.13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
8-Feb-12,2038,0,0,4,4,.2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
9-Dec-11,896,0,3,1,4,.45                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
21-Dec-11,1200,0,4,5,9,.75                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
1-Feb-12,1858,0,0,1,1,.05                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
4-Jan-12,923,0,1,2,3,.33                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
25-Jan-12,1667,0,0,3,3,.18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
18-Jan-12,1222,0,0,2,2,.16                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
15-Dec-11,624,0,4,5,9,1.44                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
13-Dec-11,2188,3,1,0,4,.18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
11-Jan-12,1076,0,0,0,0,0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
15-Feb-12,2527,0,0,0,0,0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
8-Dec-11,144,0,0,0,0,0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
Total Mailed,17849,,,,41,.23                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

Expected output
**********************
Mail File,Count,Sales Dec 2011,Sales Jan 2012,Sales Feb 2012,Total Sales,Percent of Sale                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
8-Dec-11,144,0,0,0,0,0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
9-Dec-11,896,0,3,1,4,.45                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
13-Dec-11,2188,3,1,0,4,.18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
15-Dec-11,624,0,4,5,9,1.44                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
21-Dec-11,1200,0,4,5,9,.75                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
28-Dec-11,1486,0,2,0,2,.13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
4-Jan-12,923,0,1,2,3,.33                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
11-Jan-12,1076,0,0,0,0,0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
18-Jan-12,1222,0,0,2,2,.16                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
25-Jan-12,1667,0,0,3,3,.18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
1-Feb-12,1858,0,0,1,1,.05                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
8-Feb-12,2038,0,0,4,4,.2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
15-Feb-12,2527,0,0,0,0,0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
Total Mailed,17849,3,15,23,41,.23                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    



Re: CSV report [message #543976 is a reply to message #543973] Sat, 18 February 2012 10:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
The following is generic and ordered and assumes that you want the current month and the previous two months, as in your example.

SCOTT@orcl_11gR2> SELECT 'Mail File'	   ||','||
  2  	    'Count'	      ||','||
  3  	    'Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -2), 'Mon YYYY')	||','||
  4  	    'Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'Mon YYYY')	||','||
  5  	    'Sales ' || TO_CHAR (SYSDATE, 'Mon YYYY')  ||','||
  6  	    'Total Sales'     ||','||
  7  	    'Percent of Sale'
  8  FROM DUAL
  9  union all
 10  select *
 11  from   (SELECT to_char(TRUNC (m.added_date),'dd-Mon-yyyy') ||','||
 12  		    m.record_cnt ||','||
 13  		    nvl(b.two_months_prior,0)  ||','||
 14  		    nvl(b.one_month_prior,0) ||','||
 15  		    nvl(b.this_month,0)  ||','||
 16  		    nvl(b."Total Sales",0)  ||','||
 17  		    nvl(b."sales_percent",0)
 18  	     FROM   zam_co_extract_log m,
 19  		    (SELECT  tab1.extract_id,
 20  			     SUM (CASE WHEN tab1.applied_mon = TO_CHAR (ADD_MONTHS (SYSDATE, -2), 'MON-YYYY')
 21  				       THEN cnt ELSE 0	END) two_months_prior,
 22  			     SUM (CASE WHEN tab1.applied_mon = TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'MON-YYYY')
 23  				       THEN cnt ELSE 0	END) one_month_prior,
 24  			     SUM (CASE WHEN tab1.applied_mon = TO_CHAR (SYSDATE, 'MON-YYYY')
 25  				       THEN cnt ELSE 0	END) this_month,
 26  			     SUM (cnt) "Total Sales"  ,
 27  			     round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent"
 28  		     FROM    (SELECT  extract_id, added_date, applied_mon, COUNT (1) cnt
 29  			      FROM    (SELECT l.extract_id, TRUNC (l.added_date) added_date,
 30  					      TO_CHAR (drr.applied_date, 'MON') || '-' ||
 31  					      TO_CHAR (drr.applied_date, 'YYYY') applied_mon
 32  				       FROM   zam_co_extract_log l,
 33  					      plc_sr_au_drrs_hist drr,
 34  					      zam_sr_au_incentive_cust_hist inc
 35  				       WHERE  TRIM (UPPER (inc.addr1)) =
 36  					      TRIM (UPPER (drr.addr1))
 37  				       AND    l.extract_id = inc.extract_id
 38  				       AND    drr.applied_date > inc.added_date
 39  				       AND    drr.reject_code IS NULL)
 40  			      GROUP   BY extract_id,added_date, applied_mon) tab1,
 41  			     (SELECT TRUNC (added_date) added_date, record_cnt,extract_id
 42  			      FROM   zam_co_extract_log) tab2
 43  		     WHERE   tab1.added_date = tab2.added_date
 44  		     GROUP   BY tab1.extract_id,tab2.record_cnt) b
 45  	     where   m.extract_id = b.extract_id(+)
 46  	     order   by m.added_date)
 47  union all
 48  select 'Total Mailed'||','|| rec_cnt||','|| null||','|| null||','||null||','|| cnt||','
 49  	    || round(cnt/(rec_cnt)*100,2)
 50  from   (SELECT count(1) cnt
 51  	     FROM   zam_co_extract_log l,
 52  		    plc_sr_au_drrs_hist drr,
 53  		    zam_sr_au_incentive_cust_hist inc
 54  	     WHERE  TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1))
 55  	     AND    l.extract_id = inc.extract_id
 56  	     AND    drr.applied_date > inc.added_date
 57  	     AND    drr.reject_code IS NULL),
 58  	    (SELECT sum(record_cnt) rec_cnt
 59  	     FROM   zam_co_extract_log)
 60  /
Mail File,Count,Sales Dec 2011,Sales Jan 2012,Sales Feb 2012,Total Sales,Percent of Sale
08-Dec-2011,144,0,0,0,0,0
09-Dec-2011,896,0,3,1,4,.45
13-Dec-2011,2188,3,1,0,4,.18
15-Dec-2011,624,0,4,5,9,1.44
21-Dec-2011,1200,0,4,5,9,.75
28-Dec-2011,1486,0,2,0,2,.13
04-Jan-2012,923,0,1,2,3,.33
11-Jan-2012,1076,0,0,0,0,0
18-Jan-2012,1222,0,0,2,2,.16
25-Jan-2012,1667,0,0,3,3,.18
01-Feb-2012,1858,0,0,1,1,.05
08-Feb-2012,2038,0,0,4,4,.2
15-Feb-2012,2527,0,0,0,0,0
Total Mailed,17849,,,,41,.23

Re: CSV report [message #544030 is a reply to message #543976] Sun, 19 February 2012 08:08 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Hi,

As given in my expected output, it should have grand total of each month in the last row,also in the output it should have data for all the months from Dec 2011, as we are in Feb 2012, my output has only Dec 2011,Jan 2012, Feb 2012, in Mar 2012 it should include Dec 2011,Jan 2012, Feb 2012 & Mar 2012, in Apr 2012 it should include Dec 2011,Jan 2012, Feb 2012, Mar 2012 & Apr 2012.

As Mar 2012, data does not exist I did not provide any data, also I provided data which match the criteria.

I am looking for a solution which will run every month without modifying the query, the solution can be single query or procedure.

Thanks
Raghu

[Updated on: Sun, 19 February 2012 08:12]

Report message to a moderator

Re: CSV report [message #544033 is a reply to message #544030] Sun, 19 February 2012 08:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
If the number of months is unknown, then you will need to generate the query dynamically, either using SQL to create SQL or PL/SQL with execute immediate.
Re: CSV report [message #544035 is a reply to message #544033] Sun, 19 February 2012 08:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
If you search the forum for things like "dynamic pivot", you can find some examples. Here is one:

http://www.orafaq.com/forum/t/168509/0/
Re: CSV report [message #544037 is a reply to message #544035] Sun, 19 February 2012 09:14 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Even if we write a dynamic pivot,the output should be spooled to specific location (ex: c:\enterprise_report_feb2012.csv)

If we create a procedure with execute immediate, how can I direct the output as spool file to specific location.

Thanks
Raghu
Re: CSV report [message #544040 is a reply to message #544037] Sun, 19 February 2012 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
> how can I direct the output as spool file to specific location
The output file will be named & located where you specify it should reside.
Re: CSV report [message #544041 is a reply to message #544040] Sun, 19 February 2012 09:30 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
can you please give an example of how it can be done.
Re: CSV report [message #544042 is a reply to message #544041] Sun, 19 February 2012 09:36 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
http://www.oracle.com/pls/db112/search?remark=quick_search&word=utl_file
Re: CSV report [message #544043 is a reply to message #544042] Sun, 19 February 2012 10:09 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
By using utl_file, we can write to external file to any location.

In my requirement, I have to write a dynamic query, I assume using execute immediate of dynamic query
we can get result or declaring sys_refcusor we can return results of dynamic query.

How can I assign the output to utl_file.putf ?

Few of the examples, I have found in google have static cursors and looped the cursor results to utl_file.putf.

Re: CSV report [message #544045 is a reply to message #544043] Sun, 19 February 2012 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
post working example that shows how you utilize sys_refcursor

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: CSV report [message #544054 is a reply to message #544045] Sun, 19 February 2012 12:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
-- complete script:
-- generate dynamic sql and open ref cursor using that sql:
VARIABLE g_refcursor REFCURSOR
DECLARE
  v_months  NUMBER;
  v_sql     CLOB;
BEGIN
  SELECT MONTHS_BETWEEN (SYSDATE, MIN (applied_date))
  INTO   v_months
  FROM   plc_sr_au_drrs_hist;
  v_sql := 
    'SELECT ''Mail File,Count';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ',Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'Mon YYYY'); 
  END LOOP;
  v_sql := v_sql 
  || ',Total Sales,Percent of Sale'' FROM DUAL '
  || 'UNION ALL '
  || 'SELECT * '
  || 'FROM (SELECT TO_CHAR (TRUNC (m.added_date), ''dd-Mon-yyyy'') || '','' ||'
  || 'm.record_cnt || '','' || ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'NVL (b.month' || i || ', 0) || '','' || '; 
  END LOOP;
  v_sql := v_sql 
  || 'NVL (b."Total Sales", 0) || '','' || NVL (b."sales_percent", 0) '
  || 'FROM zam_co_extract_log m,'
  || '(SELECT tab1.extract_id,';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (CASE WHEN tab1.applied_mon = '
    || 'TO_CHAR (ADD_MONTHS (SYSDATE, -' || i || '), ''MON-YYYY'')'
    || 'THEN cnt ELSE 0  END) month' || i || ',';
  END LOOP;
  v_sql := v_sql 
  || 'SUM (cnt) "Total Sales",'  
  || 'round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent" '
  || 'FROM (SELECT extract_id, added_date, applied_mon, COUNT (1) cnt '
  || 'FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,'
  || 'TO_CHAR (drr.applied_date, ''MON'') || ''-'' || ' 
  || 'TO_CHAR (drr.applied_date, ''YYYY'') applied_mon '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '                                             
  || 'AND drr.reject_code IS NULL) '
  || 'GROUP BY extract_id,added_date, applied_mon) tab1,'
  || '(SELECT TRUNC (added_date) added_date, record_cnt,extract_id ' 
  || 'FROM zam_co_extract_log) tab2 '
  || 'WHERE tab1.added_date = tab2.added_date '
  || 'GROUP BY tab1.extract_id,tab2.record_cnt) b '
  || 'where m.extract_id = b.extract_id(+) '
  || 'order by m.added_date)'
  || ' union all '
  || 'select ''Total Mailed,'' || rec_cnt ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ' || '','' || month' || i;
  END LOOP;
  v_sql := v_sql 
  || '||'',''|| cnt||'',''|| round(cnt/(rec_cnt)*100,2) '
  || 'from '
  || '(SELECT ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (DECODE (TO_CHAR (drr.applied_date, ''MON-YYYY''), '''
    || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON-YYYY')
    || ''', 1)) month' || i || ',';
  END LOOP;
  v_sql := v_sql
  || ' count(1) cnt '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '
  || 'AND drr.reject_code IS NULL),'         
  || '(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)';
  OPEN :g_refcursor FOR v_sql;
END;
/
-- store current settings before spooling:
STORE SET saved_settings REPLACE
-- settings before spooling:
SET LINESIZE 2000
SET SPACE       0
SET TERM     OFF
SET HEAD     OFF
SET PAGESIZE    0
SET ARRAYSIZE   1
SET FEEDBACK OFF
SET EMBEDDED ON
SET ECHO     OFF
-- generate dynamic spool file name:
COLUMN filename NEW_VALUE spoolfilename
SELECT 'c:\enterprise_report_' 
       || to_char (sysdate, 'monyyyy')
       || '.csv' 
       AS filename
FROM   DUAL
/
-- spool and print contents of refcursor:
SPOOL &spoolfilename
PRINT g_refcursor
SPOOL OFF
-- restore previous settings:
START saved_settings


-- resulting file c:\enterprise_report_feb2012.csv:
Mail File,Count,Sales Dec 2011,Sales Jan 2012,Sales Feb 2012,Total Sales,Percent of Sale
08-Dec-2011,144,0,0,0,0,0
09-Dec-2011,896,0,3,1,4,.45
13-Dec-2011,2188,3,1,0,4,.18
15-Dec-2011,624,0,4,5,9,1.44
21-Dec-2011,1200,0,4,5,9,.75
28-Dec-2011,1486,0,2,0,2,.13
04-Jan-2012,923,0,1,2,3,.33
11-Jan-2012,1076,0,0,0,0,0
18-Jan-2012,1222,0,0,2,2,.16
25-Jan-2012,1667,0,0,3,3,.18
01-Feb-2012,1858,0,0,1,1,.05
08-Feb-2012,2038,0,0,4,4,.2
15-Feb-2012,2527,0,0,0,0,0
Total Mailed,17849,3,15,23,41,.23

[Updated on: Sun, 19 February 2012 12:34]

Report message to a moderator

Re: CSV report [message #544245 is a reply to message #544054] Mon, 20 February 2012 21:33 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Thanks Barbara for your solutions.

I have created a procedure and spooled the output to specific location with set commands, if I have to use utl_file to re-direct the output, how should it be done.


create or replace procedure enterprise_report(v_refcursor out sys_refcursor)
is
  v_months  NUMBER;
  v_sql     varchar2(32676); 
BEGIN
  SELECT MONTHS_BETWEEN (SYSDATE, MIN (applied_date))
  INTO   v_months
  FROM   plc_sr_au_drrs_hist;
  v_sql := 
    'SELECT ''Mail File,Count';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ',Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'Mon YYYY'); 
  END LOOP;
  v_sql := v_sql 
  || ',Total Sales,Percent of Sale'' FROM DUAL '
  || 'UNION ALL '
  || 'SELECT * '
  || 'FROM (SELECT TO_CHAR (TRUNC (m.added_date), ''dd-Mon-yyyy'') || '','' ||'
  || 'm.record_cnt || '','' || ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'NVL (b.month' || i || ', 0) || '','' || '; 
  END LOOP;
  v_sql := v_sql 
  || 'NVL (b."Total Sales", 0) || '','' || NVL (b."sales_percent", 0) '
  || 'FROM zam_co_extract_log m,'
  || '(SELECT tab1.extract_id,';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (CASE WHEN tab1.applied_mon = '
    || 'TO_CHAR (ADD_MONTHS (SYSDATE, -' || i || '), ''MON-YYYY'')'
    || 'THEN cnt ELSE 0  END) month' || i || ',';
  END LOOP;
  v_sql := v_sql 
  || 'SUM (cnt) "Total Sales",'  
  || 'round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent" '
  || 'FROM (SELECT extract_id, added_date, applied_mon, COUNT (1) cnt '
  || 'FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,'
  || 'TO_CHAR (drr.applied_date, ''MON'') || ''-'' || ' 
  || 'TO_CHAR (drr.applied_date, ''YYYY'') applied_mon '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '                                             
  || 'AND drr.reject_code IS NULL) '
  || 'GROUP BY extract_id,added_date, applied_mon) tab1,'
  || '(SELECT TRUNC (added_date) added_date, record_cnt,extract_id ' 
  || 'FROM zam_co_extract_log) tab2 '
  || 'WHERE tab1.added_date = tab2.added_date '
  || 'GROUP BY tab1.extract_id,tab2.record_cnt) b '
  || 'where m.extract_id = b.extract_id(+) '
  || 'order by m.added_date)'
  || ' union all '
  || 'select ''Total Mailed,'' || rec_cnt ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ' || '','' || month' || i;
  END LOOP;
  v_sql := v_sql 
  || '||'',''|| cnt||'',''|| round(cnt/(rec_cnt)*100,2) '
  || 'from '
  || '(SELECT ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (DECODE (TO_CHAR (drr.applied_date, ''MON-YYYY''), '''
    || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON-YYYY')
    || ''', 1)) month' || i || ',';
  END LOOP;
  v_sql := v_sql
  || ' count(1) cnt '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '
  || 'AND drr.reject_code IS NULL),'         
  || '(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)';
  OPEN v_refcursor FOR v_sql;
END;
/

var a refcursor
SET LINESIZE 2000
SET SPACE       0
SET TERM     OFF
SET HEAD     OFF
SET PAGESIZE    0
SET ARRAYSIZE   1
SET FEEDBACK OFF
SET EMBEDDED ON
SET ECHO     OFF
-- generate dynamic spool file name:
COLUMN filename NEW_VALUE spoolfilename
SELECT 'c:\enterprise_report_' 
       || to_char (sysdate, 'monyyyy')
       || '.csv' 
       AS filename
FROM   DUAL
/
exec enterprise_report(:a)
-- spool and print contents of refcursor:
SPOOL &spoolfilename
PRINT a
SPOOL OFF

Re: CSV report [message #544262 is a reply to message #544245] Mon, 20 February 2012 23:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
To use utl_file, you will need to create an Oracle directory object that uses the path of the operating system directory on your server that you want to write the file to.
You will need to grant read and write privileges on that directory object to the creator of the procedure.
Oracle must have read and write privileges to the operating system directory.

In the example below, I have created an Oracle directory object named my_dir that uses the operating system directory c:\my_oracle_files on my server.
Note that this directory object is created in upper case by default, so it must be passed in upper case.
I have made it the default value of the input parameter for the procedure, so it does not need to be passed, but can be.

-- script for creating procedure,
-- creating Oracle directory object,
-- and execution of procedure:
CREATE OR REPLACE PROCEDURE enterprise_report
  (p_dir IN VARCHAR2 DEFAULT 'MY_DIR')
AS
  v_months     NUMBER;
  v_sql        VARCHAR2 (32767); 
  v_filename   VARCHAR2 (29);
  v_file       UTL_FILE.FILE_TYPE;
  v_refcursor  SYS_REFCURSOR;
  v_row        VARCHAR2 (4000);
BEGIN
  SELECT MONTHS_BETWEEN (SYSDATE, MIN (applied_date))
  INTO   v_months
  FROM   plc_sr_au_drrs_hist;
  v_sql := 
    'SELECT ''Mail File,Count';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ',Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'Mon YYYY'); 
  END LOOP;
  v_sql := v_sql 
  || ',Total Sales,Percent of Sale'' FROM DUAL '
  || 'UNION ALL '
  || 'SELECT * '
  || 'FROM (SELECT TO_CHAR (TRUNC (m.added_date), ''dd-Mon-yyyy'') || '','' ||'
  || 'm.record_cnt || '','' || ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'NVL (b.month' || i || ', 0) || '','' || '; 
  END LOOP;
  v_sql := v_sql 
  || 'NVL (b."Total Sales", 0) || '','' || NVL (b."sales_percent", 0) '
  || 'FROM zam_co_extract_log m,'
  || '(SELECT tab1.extract_id,';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (CASE WHEN tab1.applied_mon = '
    || 'TO_CHAR (ADD_MONTHS (SYSDATE, -' || i || '), ''MON-YYYY'')'
    || 'THEN cnt ELSE 0  END) month' || i || ',';
  END LOOP;
  v_sql := v_sql 
  || 'SUM (cnt) "Total Sales",'  
  || 'round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent" '
  || 'FROM (SELECT extract_id, added_date, applied_mon, COUNT (1) cnt '
  || 'FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,'
  || 'TO_CHAR (drr.applied_date, ''MON'') || ''-'' || ' 
  || 'TO_CHAR (drr.applied_date, ''YYYY'') applied_mon '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '                                             
  || 'AND drr.reject_code IS NULL) '
  || 'GROUP BY extract_id,added_date, applied_mon) tab1,'
  || '(SELECT TRUNC (added_date) added_date, record_cnt,extract_id ' 
  || 'FROM zam_co_extract_log) tab2 '
  || 'WHERE tab1.added_date = tab2.added_date '
  || 'GROUP BY tab1.extract_id,tab2.record_cnt) b '
  || 'where m.extract_id = b.extract_id(+) '
  || 'order by m.added_date)'
  || ' union all '
  || 'select ''Total Mailed,'' || rec_cnt ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ' || '','' || month' || i;
  END LOOP;
  v_sql := v_sql 
  || '||'',''|| cnt||'',''|| round(cnt/(rec_cnt)*100,2) '
  || 'from '
  || '(SELECT ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (DECODE (TO_CHAR (drr.applied_date, ''MON-YYYY''), '''
    || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON-YYYY')
    || ''', 1)) month' || i || ',';
  END LOOP;
  v_sql := v_sql
  || ' count(1) cnt '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '
  || 'AND drr.reject_code IS NULL),'         
  || '(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)';
  v_filename := 'enterprise_report_' || TO_CHAR (SYSDATE, 'MONYYYY') || '.csv';   
  v_file := UTL_FILE.FOPEN (p_dir, v_filename, 'W', 4000);
  OPEN v_refcursor FOR v_sql;
  LOOP
    FETCH v_refcursor INTO v_row;
    EXIT WHEN v_refcursor%NOTFOUND;
    UTL_FILE.PUT_LINE (v_file, v_row);
  END LOOP;
  UTL_FILE.FFLUSH (v_file);
  UTL_FILE.FCLOSE (v_file);
  CLOSE v_refcursor;
END enterprise_report;
/
SHOW ERRORS
CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
/
EXEC enterprise_report


-- execution of script:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE enterprise_report
  2    (p_dir IN VARCHAR2 DEFAULT 'MY_DIR')
  3  AS
  4    v_months     NUMBER;
  5    v_sql	    VARCHAR2 (32767);
  6    v_filename   VARCHAR2 (29);
  7    v_file	    UTL_FILE.FILE_TYPE;
  8    v_refcursor  SYS_REFCURSOR;
  9    v_row	    VARCHAR2 (4000);
 10  BEGIN
 11    SELECT MONTHS_BETWEEN (SYSDATE, MIN (applied_date))
 12    INTO   v_months
 13    FROM   plc_sr_au_drrs_hist;
 14    v_sql :=
 15  	 'SELECT ''Mail File,Count';
 16    FOR i IN REVERSE 0 .. v_months LOOP
 17  	 v_sql := v_sql
 18  	 || ',Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'Mon YYYY');
 19    END LOOP;
 20    v_sql := v_sql
 21    || ',Total Sales,Percent of Sale'' FROM DUAL '
 22    || 'UNION ALL '
 23    || 'SELECT * '
 24    || 'FROM (SELECT TO_CHAR (TRUNC (m.added_date), ''dd-Mon-yyyy'') || '','' ||'
 25    || 'm.record_cnt || '','' || ';
 26    FOR i IN REVERSE 0 .. v_months LOOP
 27  	 v_sql := v_sql
 28  	 || 'NVL (b.month' || i || ', 0) || '','' || ';
 29    END LOOP;
 30    v_sql := v_sql
 31    || 'NVL (b."Total Sales", 0) || '','' || NVL (b."sales_percent", 0) '
 32    || 'FROM zam_co_extract_log m,'
 33    || '(SELECT tab1.extract_id,';
 34    FOR i IN REVERSE 0 .. v_months LOOP
 35  	 v_sql := v_sql
 36  	 || 'SUM (CASE WHEN tab1.applied_mon = '
 37  	 || 'TO_CHAR (ADD_MONTHS (SYSDATE, -' || i || '), ''MON-YYYY'')'
 38  	 || 'THEN cnt ELSE 0  END) month' || i || ',';
 39    END LOOP;
 40    v_sql := v_sql
 41    || 'SUM (cnt) "Total Sales",'
 42    || 'round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent" '
 43    || 'FROM (SELECT extract_id, added_date, applied_mon, COUNT (1) cnt '
 44    || 'FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,'
 45    || 'TO_CHAR (drr.applied_date, ''MON'') || ''-'' || '
 46    || 'TO_CHAR (drr.applied_date, ''YYYY'') applied_mon '
 47    || 'FROM zam_co_extract_log l,'
 48    || 'plc_sr_au_drrs_hist drr,'
 49    || 'zam_sr_au_incentive_cust_hist inc '
 50    || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
 51    || 'AND l.extract_id = inc.extract_id '
 52    || 'AND drr.applied_date > inc.added_date '
 53    || 'AND drr.reject_code IS NULL) '
 54    || 'GROUP BY extract_id,added_date, applied_mon) tab1,'
 55    || '(SELECT TRUNC (added_date) added_date, record_cnt,extract_id '
 56    || 'FROM zam_co_extract_log) tab2 '
 57    || 'WHERE tab1.added_date = tab2.added_date '
 58    || 'GROUP BY tab1.extract_id,tab2.record_cnt) b '
 59    || 'where m.extract_id = b.extract_id(+) '
 60    || 'order by m.added_date)'
 61    || ' union all '
 62    || 'select ''Total Mailed,'' || rec_cnt ';
 63    FOR i IN REVERSE 0 .. v_months LOOP
 64  	 v_sql := v_sql
 65  	 || ' || '','' || month' || i;
 66    END LOOP;
 67    v_sql := v_sql
 68    || '||'',''|| cnt||'',''|| round(cnt/(rec_cnt)*100,2) '
 69    || 'from '
 70    || '(SELECT ';
 71    FOR i IN REVERSE 0 .. v_months LOOP
 72  	 v_sql := v_sql
 73  	 || 'SUM (DECODE (TO_CHAR (drr.applied_date, ''MON-YYYY''), '''
 74  	 || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON-YYYY')
 75  	 || ''', 1)) month' || i || ',';
 76    END LOOP;
 77    v_sql := v_sql
 78    || ' count(1) cnt '
 79    || 'FROM zam_co_extract_log l,'
 80    || 'plc_sr_au_drrs_hist drr,'
 81    || 'zam_sr_au_incentive_cust_hist inc '
 82    || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
 83    || 'AND l.extract_id = inc.extract_id '
 84    || 'AND drr.applied_date > inc.added_date '
 85    || 'AND drr.reject_code IS NULL),'
 86    || '(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)';
 87    v_filename := 'enterprise_report_' || TO_CHAR (SYSDATE, 'MONYYYY') || '.csv';
 88    v_file := UTL_FILE.FOPEN (p_dir, v_filename, 'W', 4000);
 89    OPEN v_refcursor FOR v_sql;
 90    LOOP
 91  	 FETCH v_refcursor INTO v_row;
 92  	 EXIT WHEN v_refcursor%NOTFOUND;
 93  	 UTL_FILE.PUT_LINE (v_file, v_row);
 94    END LOOP;
 95    UTL_FILE.FFLUSH (v_file);
 96    UTL_FILE.FCLOSE (v_file);
 97    CLOSE v_refcursor;
 98  END enterprise_report;
 99  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> EXEC enterprise_report

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2>


-- resulting file c:\my_oracle_files\enterprise_report_feb2012.csv:
Mail File,Count,Sales Dec 2011,Sales Jan 2012,Sales Feb 2012,Total Sales,Percent of Sale
08-Dec-2011,144,0,0,0,0,0
09-Dec-2011,896,0,3,1,4,.45
13-Dec-2011,2188,3,1,0,4,.18
15-Dec-2011,624,0,4,5,9,1.44
21-Dec-2011,1200,0,4,5,9,.75
28-Dec-2011,1486,0,2,0,2,.13
04-Jan-2012,923,0,1,2,3,.33
11-Jan-2012,1076,0,0,0,0,0
18-Jan-2012,1222,0,0,2,2,.16
25-Jan-2012,1667,0,0,3,3,.18
01-Feb-2012,1858,0,0,1,1,.05
08-Feb-2012,2038,0,0,4,4,.2
15-Feb-2012,2527,0,0,0,0,0
Total Mailed,17849,3,15,23,41,.23





[Updated on: Mon, 20 February 2012 23:49]

Report message to a moderator

Re: CSV report [message #544291 is a reply to message #544262] Tue, 21 February 2012 02:12 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Hi Barbara,

I have a question, as this requirement is for csv file, for utl_file, v_row variable is declared and each row is assgined to that variable.

If requirement is not csv file, for spool file output we couldn't have concatenated (||','||) and still get the output.

But if we have to do in utl_file, how we can do it, as we don't number of months until run time.

Thanks
Raghu
Re: CSV report [message #544295 is a reply to message #544291] Tue, 21 February 2012 02:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
Your question is unclear. I have demonstrated that what I provided works for the output format that you requested. Do you now want some sort of different output? If you want aligned columns, then you can use lpad and/or rpad in your concatenation. Otherwise, you would need to use dbms_sql to define columns.








Re: CSV report [message #544607 is a reply to message #544295] Wed, 22 February 2012 23:07 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
Hi Barbara,

In earlier example of utl_file as expected output is csv file separated by comma, v_row is defined as single variable and assigned each row of the loop to v_row and wrote to utl_file.put_line.

If the output is not separated by any specific delimiter, how can we achieve the similar output using utl_file as we don't know number of months until run time.

As line size is given as 2000 in spool file, each row is not displayed in single line.


create or replace procedure enterprise_report(v_refcursor out sys_refcursor) is
v_months    number;
v_sql       varchar2(4000);
begin
select months_between(sysdate,min(added_date))
into v_months
from zam_co_extract_log;
v_sql := 'select rpad(''Mail File'',20), rpad(''Count'',10)';
for i in reverse 0 .. v_months
loop
v_sql := v_sql || ', ''Sales '|| to_char(add_months(sysdate, -i),'Mon YYYY') || '''';
end loop;
v_sql := v_sql || ',''Total Sales'', ''Percent of sale''' ;
v_sql := v_sql || ' From dual';
v_sql := v_sql ||' union all ';
v_sql := v_sql || 'SELECT * '
     || 'FROM (SELECT TO_CHAR (TRUNC (m.added_date), ''dd-Mon-yyyy'') ,'
     || 'to_char(m.record_cnt) , ';
     FOR i IN REVERSE 0 .. v_months LOOP
        v_sql := v_sql
        || 'to_char(NVL (b.month' || i || ', 0)) , ';
     END LOOP;
     v_sql := v_sql
     || 'to_char(NVL (b."Total Sales", 0)) , to_char(NVL (b."sales_percent", 0)) '
     || 'FROM zam_co_extract_log m,'
    || '(SELECT tab1.extract_id,';
    FOR i IN REVERSE 0 .. v_months LOOP
       v_sql := v_sql
       || 'SUM (CASE WHEN tab1.applied_mon = '
       || 'TO_CHAR (ADD_MONTHS (SYSDATE, -' || i || '), ''MON-YYYY'')'
        || 'THEN cnt ELSE 0  END) month' || i || ',';
     END LOOP;
     v_sql := v_sql
     || 'SUM (cnt) "Total Sales",'
     || 'round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent" '
     || 'FROM (SELECT extract_id, added_date, applied_mon, COUNT (1) cnt '
     || 'FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,'
     || 'TO_CHAR (drr.applied_date, ''MON-YYYY'') applied_mon '    
     || 'FROM zam_co_extract_log l,'
     || 'plc_sr_au_drrs_hist drr,'
     || 'zam_sr_au_incentive_cust_hist inc '
     || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
     || 'AND l.extract_id = inc.extract_id '
     || 'AND drr.applied_date > inc.added_date '
     || 'AND drr.reject_code IS NULL) '
     || 'GROUP BY extract_id,added_date, applied_mon) tab1,'
     || '(SELECT TRUNC (added_date) added_date, record_cnt,extract_id '
     || 'FROM zam_co_extract_log) tab2 '
     || 'WHERE tab1.added_date = tab2.added_date '
     || 'GROUP BY tab1.extract_id,tab2.record_cnt) b '
     || 'where m.extract_id = b.extract_id(+) '
     || 'order by m.added_date)';
v_sql :=  v_sql 
  ||' union all '
  || 'select ''Total Mailed'' , to_char(rec_cnt) ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ', to_char(month'|| i ||')'   ;
  END LOOP;
  v_sql := v_sql 
  || ', to_char(cnt), to_char(round(cnt/(rec_cnt)*100,2)) '
  || 'from '
  || '(SELECT ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (DECODE (TO_CHAR (drr.applied_date, ''MON-YYYY''), '''
    || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON-YYYY')
    || ''', 1)) month' || i || ',';
  END LOOP;
  v_sql := v_sql
  || ' count(1) cnt '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '
  || 'AND drr.reject_code IS NULL),'         
  || '(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)';
open v_refcursor for v_sql;
end;

VAR a refcursor
SET LINESIZE 2000
SET SPACE       0
SET TERM     OFF
SET HEAD     OFF
SET PAGESIZE    0
SET ARRAYSIZE   1
SET FEEDBACK OFF
SET EMBEDDED ON
SET ECHO     OFF
COLUMN filename NEW_VALUE spoolfilename
SELECT 'c:\enterprise_report_' 
       || to_char (sysdate, 'ddmonyyyyhhmi')
       || '.txt' 
       AS filename
FROM   DUAL
/
SPOOL &spoolfilename
exec enterprise_report(:a);
print a
spool off

Output of spool file
**********************

Mail File           Count                                   Sales Dec 2011                          Sales Jan 2012                          Sales Feb 2012                          Total Sales                             Percent of sale                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
08-Dec-2011         144                                     0                                       0                                       0                                       0                                       0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
09-Dec-2011         896                                     0                                       3                                       1                                       4                                       .45                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
13-Dec-2011         2188                                    3                                       1                                       0                                       4                                       .18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
15-Dec-2011         624                                     0                                       4                                       5                                       9                                       1.44                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
21-Dec-2011         1200                                    0                                       4                                       5                                       9                                       .75                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
28-Dec-2011         1486                                    0                                       2                                       0                                       2                                       .13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
04-Jan-2012         923                                     0                                       1                                       2                                       3                                       .33                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
11-Jan-2012         1076                                    0                                       0                                       0                                       0                                       0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
18-Jan-2012         1222                                    0                                       0                                       2                                       2                                       .16                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
25-Jan-2012         1667                                    0                                       0                                       3                                       3                                       .18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
01-Feb-2012         1858                                    0                                       0                                       1                                       1                                       .05                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
08-Feb-2012         2038                                    0                                       0                                       4                                       4                                       .2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
15-Feb-2012         2527                                    0                                       0                                       0                                       0                                       0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
Total Mailed        17849                                   3                                       15                                      23                                      41                                      .23              



Thanks
Raghu
Re: CSV report [message #544609 is a reply to message #544607] Wed, 22 February 2012 23:36 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
Set your linesize larger and/or make your columns narrower.
Re: CSV report [message #544622 is a reply to message #544609] Thu, 23 February 2012 01:52 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
In text pad or word pad, each row is displayed in single line, when I copy the output in the forum, each row is shown in multiple line.
Re: CSV report [message #544734 is a reply to message #544622] Thu, 23 February 2012 09:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
So, does that mean your problem is solved?
Re: CSV report [message #544813 is a reply to message #544734] Thu, 23 February 2012 23:52 Go to previous messageGo to next message
tedlaraghu
Messages: 51
Registered: December 2011
Member
If I have to get the same output using utl_file, what changes I need to do, as we don't know number of months until dynamic sql is executed and the output should not have any delimiter.
Re: CSV report [message #544885 is a reply to message #544813] Fri, 24 February 2012 08:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
Take the last code that I provided, remove the commas from the concatenation, and use lpad and/or rpad around the column values to align them.
Re: CSV report [message #544918 is a reply to message #544885] Fri, 24 February 2012 16:38 Go to previous message
Barbara Boehmer
Messages: 7973
Registered: November 2002
Location: California, USA
Senior Member
The following has been modified as I described.

-- script:
CREATE OR REPLACE PROCEDURE enterprise_report
  (p_dir IN VARCHAR2 DEFAULT 'MY_DIR')
AS
  v_months     NUMBER;
  v_sql        VARCHAR2 (32767); 
  v_filename   VARCHAR2 (29);
  v_file       UTL_FILE.FILE_TYPE;
  v_refcursor  SYS_REFCURSOR;
  v_row        VARCHAR2 (4000);
BEGIN
  SELECT MONTHS_BETWEEN (SYSDATE, MIN (applied_date))
  INTO   v_months
  FROM   plc_sr_au_drrs_hist;
  v_sql := 
    'SELECT ''Mail File     Count';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || '  Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'Mon YYYY'); 
  END LOOP;
  v_sql := v_sql 
  || '  Total Sales  Percent of Sale'' FROM DUAL '
  || 'UNION ALL '
  || 'SELECT * '
  || 'FROM (SELECT TO_CHAR (TRUNC (m.added_date), ''dd-Mon-yyyy'') || ''   '' ||'
  || 'LPAD (m.record_cnt, 5) || ''  '' || ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'LPAD (NVL (b.month' || i || ', 0), 14) || ''  '' || '; 
  END LOOP;
  v_sql := v_sql 
  || 'LPAD (NVL (b."Total Sales", 0), 11) || ''  '' || LPAD (NVL (b."sales_percent", 0), 15) '
  || 'FROM zam_co_extract_log m,'
  || '(SELECT tab1.extract_id,';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (CASE WHEN tab1.applied_mon = '
    || 'TO_CHAR (ADD_MONTHS (SYSDATE, -' || i || '), ''MON-YYYY'')'
    || 'THEN cnt ELSE 0  END) month' || i || ',';
  END LOOP;
  v_sql := v_sql 
  || 'SUM (cnt) "Total Sales",'  
  || 'round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent" '
  || 'FROM (SELECT extract_id, added_date, applied_mon, COUNT (1) cnt '
  || 'FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,'
  || 'TO_CHAR (drr.applied_date, ''MON'') || ''-'' || ' 
  || 'TO_CHAR (drr.applied_date, ''YYYY'') applied_mon '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '                                             
  || 'AND drr.reject_code IS NULL) '
  || 'GROUP BY extract_id,added_date, applied_mon) tab1,'
  || '(SELECT TRUNC (added_date) added_date, record_cnt,extract_id ' 
  || 'FROM zam_co_extract_log) tab2 '
  || 'WHERE tab1.added_date = tab2.added_date '
  || 'GROUP BY tab1.extract_id,tab2.record_cnt) b '
  || 'where m.extract_id = b.extract_id(+) '
  || 'order by m.added_date)'
  || ' union all '
  || 'select ''Total Mailed  '' || rec_cnt';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || ' || ''  '' || LPAD (month' || i || ', 14)';
  END LOOP;
  v_sql := v_sql 
  || '||''  ''|| LPAD (cnt, 11) || ''  '' || LPAD (round(cnt/(rec_cnt)*100,2), 15) '
  || 'from '
  || '(SELECT ';
  FOR i IN REVERSE 0 .. v_months LOOP
    v_sql := v_sql 
    || 'SUM (DECODE (TO_CHAR (drr.applied_date, ''MON-YYYY''), '''
    || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON-YYYY')
    || ''', 1)) month' || i || ',';
  END LOOP;
  v_sql := v_sql
  || ' count(1) cnt '
  || 'FROM zam_co_extract_log l,'
  || 'plc_sr_au_drrs_hist drr,'
  || 'zam_sr_au_incentive_cust_hist inc '
  || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
  || 'AND l.extract_id = inc.extract_id '
  || 'AND drr.applied_date > inc.added_date '
  || 'AND drr.reject_code IS NULL),'         
  || '(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)';
  v_filename := 'enterprise_report_' || TO_CHAR (SYSDATE, 'MONYYYY') || '.csv';   
  v_file := UTL_FILE.FOPEN (p_dir, v_filename, 'W', 4000);
  OPEN v_refcursor FOR v_sql;
  LOOP
    FETCH v_refcursor INTO v_row;
    EXIT WHEN v_refcursor%NOTFOUND;
    UTL_FILE.PUT_LINE (v_file, v_row);
  END LOOP;
  UTL_FILE.FFLUSH (v_file);
  UTL_FILE.FCLOSE (v_file);
  CLOSE v_refcursor;
END enterprise_report;
/
SHOW ERRORS
CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
/
EXEC enterprise_report


-- execution of script above:
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE enterprise_report
  2    (p_dir IN VARCHAR2 DEFAULT 'MY_DIR')
  3  AS
  4    v_months     NUMBER;
  5    v_sql	    VARCHAR2 (32767);
  6    v_filename   VARCHAR2 (29);
  7    v_file	    UTL_FILE.FILE_TYPE;
  8    v_refcursor  SYS_REFCURSOR;
  9    v_row	    VARCHAR2 (4000);
 10  BEGIN
 11    SELECT MONTHS_BETWEEN (SYSDATE, MIN (applied_date))
 12    INTO   v_months
 13    FROM   plc_sr_au_drrs_hist;
 14    v_sql :=
 15  	 'SELECT ''Mail File	 Count';
 16    FOR i IN REVERSE 0 .. v_months LOOP
 17  	 v_sql := v_sql
 18  	 || '  Sales ' || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'Mon YYYY');
 19    END LOOP;
 20    v_sql := v_sql
 21    || '  Total Sales  Percent of Sale'' FROM DUAL '
 22    || 'UNION ALL '
 23    || 'SELECT * '
 24    || 'FROM (SELECT TO_CHAR (TRUNC (m.added_date), ''dd-Mon-yyyy'') || ''	'' ||'
 25    || 'LPAD (m.record_cnt, 5) || ''  '' || ';
 26    FOR i IN REVERSE 0 .. v_months LOOP
 27  	 v_sql := v_sql
 28  	 || 'LPAD (NVL (b.month' || i || ', 0), 14) || ''  '' || ';
 29    END LOOP;
 30    v_sql := v_sql
 31    || 'LPAD (NVL (b."Total Sales", 0), 11) || ''  '' || LPAD (NVL (b."sales_percent", 0), 15) '
 32    || 'FROM zam_co_extract_log m,'
 33    || '(SELECT tab1.extract_id,';
 34    FOR i IN REVERSE 0 .. v_months LOOP
 35  	 v_sql := v_sql
 36  	 || 'SUM (CASE WHEN tab1.applied_mon = '
 37  	 || 'TO_CHAR (ADD_MONTHS (SYSDATE, -' || i || '), ''MON-YYYY'')'
 38  	 || 'THEN cnt ELSE 0  END) month' || i || ',';
 39    END LOOP;
 40    v_sql := v_sql
 41    || 'SUM (cnt) "Total Sales",'
 42    || 'round((SUM (cnt)/tab2.record_cnt)*100,2) "sales_percent" '
 43    || 'FROM (SELECT extract_id, added_date, applied_mon, COUNT (1) cnt '
 44    || 'FROM (SELECT l.extract_id, TRUNC (l.added_date) added_date,'
 45    || 'TO_CHAR (drr.applied_date, ''MON'') || ''-'' || '
 46    || 'TO_CHAR (drr.applied_date, ''YYYY'') applied_mon '
 47    || 'FROM zam_co_extract_log l,'
 48    || 'plc_sr_au_drrs_hist drr,'
 49    || 'zam_sr_au_incentive_cust_hist inc '
 50    || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
 51    || 'AND l.extract_id = inc.extract_id '
 52    || 'AND drr.applied_date > inc.added_date '
 53    || 'AND drr.reject_code IS NULL) '
 54    || 'GROUP BY extract_id,added_date, applied_mon) tab1,'
 55    || '(SELECT TRUNC (added_date) added_date, record_cnt,extract_id '
 56    || 'FROM zam_co_extract_log) tab2 '
 57    || 'WHERE tab1.added_date = tab2.added_date '
 58    || 'GROUP BY tab1.extract_id,tab2.record_cnt) b '
 59    || 'where m.extract_id = b.extract_id(+) '
 60    || 'order by m.added_date)'
 61    || ' union all '
 62    || 'select ''Total Mailed  '' || rec_cnt';
 63    FOR i IN REVERSE 0 .. v_months LOOP
 64  	 v_sql := v_sql
 65  	 || ' || ''  '' || LPAD (month' || i || ', 14)';
 66    END LOOP;
 67    v_sql := v_sql
 68    || '||''  ''|| LPAD (cnt, 11) || ''  '' || LPAD (round(cnt/(rec_cnt)*100,2), 15) '
 69    || 'from '
 70    || '(SELECT ';
 71    FOR i IN REVERSE 0 .. v_months LOOP
 72  	 v_sql := v_sql
 73  	 || 'SUM (DECODE (TO_CHAR (drr.applied_date, ''MON-YYYY''), '''
 74  	 || TO_CHAR (ADD_MONTHS (SYSDATE, -i), 'MON-YYYY')
 75  	 || ''', 1)) month' || i || ',';
 76    END LOOP;
 77    v_sql := v_sql
 78    || ' count(1) cnt '
 79    || 'FROM zam_co_extract_log l,'
 80    || 'plc_sr_au_drrs_hist drr,'
 81    || 'zam_sr_au_incentive_cust_hist inc '
 82    || 'WHERE TRIM (UPPER (inc.addr1)) = TRIM (UPPER (drr.addr1)) '
 83    || 'AND l.extract_id = inc.extract_id '
 84    || 'AND drr.applied_date > inc.added_date '
 85    || 'AND drr.reject_code IS NULL),'
 86    || '(SELECT sum(record_cnt) rec_cnt FROM zam_co_extract_log)';
 87    v_filename := 'enterprise_report_' || TO_CHAR (SYSDATE, 'MONYYYY') || '.csv';
 88    v_file := UTL_FILE.FOPEN (p_dir, v_filename, 'W', 4000);
 89    OPEN v_refcursor FOR v_sql;
 90    LOOP
 91  	 FETCH v_refcursor INTO v_row;
 92  	 EXIT WHEN v_refcursor%NOTFOUND;
 93  	 UTL_FILE.PUT_LINE (v_file, v_row);
 94    END LOOP;
 95    UTL_FILE.FFLUSH (v_file);
 96    UTL_FILE.FCLOSE (v_file);
 97    CLOSE v_refcursor;
 98  END enterprise_report;
 99  /

Procedure created.

SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
  2  /

Directory created.

SCOTT@orcl_11gR2> EXEC enterprise_report

PL/SQL procedure successfully completed.


-- resulting file:
Mail File     Count  Sales Dec 2011  Sales Jan 2012  Sales Feb 2012  Total Sales  Percent of Sale
08-Dec-2011     144               0               0               0            0                0
09-Dec-2011     896               0               3               1            4              .45
13-Dec-2011    2188               3               1               0            4              .18
15-Dec-2011     624               0               4               5            9             1.44
21-Dec-2011    1200               0               4               5            9              .75
28-Dec-2011    1486               0               2               0            2              .13
04-Jan-2012     923               0               1               2            3              .33
11-Jan-2012    1076               0               0               0            0                0
18-Jan-2012    1222               0               0               2            2              .16
25-Jan-2012    1667               0               0               3            3              .18
01-Feb-2012    1858               0               0               1            1              .05
08-Feb-2012    2038               0               0               4            4               .2
15-Feb-2012    2527               0               0               0            0                0
Total Mailed  17849               3              15              23           41              .23

Previous Topic: Triggers
Next Topic: A set of data after every X number of records
Goto Forum:
  


Current Time: Sat Aug 30 22:13:10 CDT 2014

Total time taken to generate the page: 0.15993 seconds