Home » SQL & PL/SQL » SQL & PL/SQL » CSV report
| CSV report [message #543968] |
Sat, 18 February 2012 08:54  |
 |
tedlaraghu
Messages: 56
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 1002 times)
|
|
|
|
|
|
|
|
| Re: CSV report [message #543973 is a reply to message #543972] |
Sat, 18 February 2012 09:29  |
|
tedlaraghu
Messages: 56
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 |
 |
Barbara Boehmer
Messages: 9090
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 |
|
tedlaraghu
Messages: 56
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 #544054 is a reply to message #544045] |
Sun, 19 February 2012 12:18 |
|
Barbara Boehmer
Messages: 9090
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 |
|
tedlaraghu
Messages: 56
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 |
|
Barbara Boehmer
Messages: 9090
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 #544607 is a reply to message #544295] |
Wed, 22 February 2012 23:07 |
|
tedlaraghu
Messages: 56
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 #544918 is a reply to message #544885] |
Fri, 24 February 2012 16:38 |
|
Barbara Boehmer
Messages: 9090
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
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 18:18:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
