anumoses
asked on
Oracle Data ( by weeks) restricting to 12 weeks.
I get the header for 12 weeks. Report is always run on a sunday date.
date from = 01-jan-2015' and ym = 201501
Header is restricted to 12 weeks. Data goes beyond 12 weeks. Header stops at 28-mar-2015. How can I restrict data for 12 weeks? Can I use CONNECT BY LEVEL <= 12 and if so where should the condition be?
header-and-data.JPG
date from = 01-jan-2015' and ym = 201501
CREATE OR REPLACE PROCEDURE load_hosp_usage_05072015(date_from IN VARCHAR, ym IN VARCHAR)
AS
v_header VARCHAR2(32767);
filename UTL_FILE.file_type;
filename1 VARCHAR2(10000);
v_procedure_c CONSTANT VARCHAR2(30) := 'load_hosp_usage';
v_location_i INTEGER;
V_error_code NUMBER;
V_error_message VARCHAR2(255);
CURSOR c1
IS
select inv_product_type,
customer_id,
patient_name,
Sun_1,
Mon_1,
Tue_1,
wed_1,
thu_1,
fri_1,
Sat_1,
Sun_1+Mon_1+Tue_1+wed_1+thu_1+fri_1+sat_1 as week_1_total,
Sun_2,
Mon_2,
Tue_2,
wed_2,
thu_2,
fri_2,
Sat_2,
Sun_2+Mon_2+Tue_2+wed_2+thu_2+fri_2+sat_2 as week_2_total,
Sun_3,
Mon_3,
Tue_3,
wed_3,
thu_3,
fri_3,
Sat_3,
Sun_3+Mon_3+Tue_3+wed_3+thu_3+fri_3+sat_3 as week_3_total,
Sun_4,
Mon_4,
Tue_4,
wed_4,
thu_4,
fri_4,
Sat_4,
Sun_4+Mon_4+Tue_4+wed_4+thu_4+fri_4+sat_4 as week_4_total,
Sun_5,
Mon_5,
Tue_5,
wed_5,
thu_5,
fri_5,
Sat_5,
Sun_5+Mon_5+Tue_5+wed_5+thu_5+fri_5+sat_5 as week_5_total,
Sun_6,
Mon_6,
Tue_6,
wed_6,
thu_6,
fri_6,
Sat_6,
Sun_6+Mon_6+Tue_6+wed_6+thu_6+fri_6+sat_6 as week_6_total,
Sun_7,
Mon_7,
Tue_7,
wed_7,
thu_7,
fri_7,
Sat_7,
Sun_7+Mon_7+Tue_7+wed_7+thu_7+fri_7+sat_7 as week_7_total,
Sun_8,
Mon_8,
Tue_8,
wed_8,
thu_8,
fri_8,
Sat_8,
Sun_8+Mon_8+Tue_8+wed_8+thu_8+fri_8+sat_8 as week_8_total,
Sun_9,
Mon_9,
Tue_9,
wed_9,
thu_9,
fri_9,
Sat_9,
Sun_9+Mon_9+Tue_9+wed_9+thu_9+fri_9+sat_9 as week_9_total,
Sun_10,
Mon_10,
Tue_10,
wed_10,
thu_10,
fri_10,
Sat_10,
Sun_10+Mon_10+Tue_10+wed_10+thu_10+fri_10+sat_10 as week_10_total,
Sun_11,
Mon_11,
Tue_11,
wed_11,
thu_11,
fri_11,
Sat_11,
Sun_11+Mon_11+Tue_11+wed_11+thu_11+fri_11+sat_11 as week_11_total,
Sun_12,
Mon_12,
Tue_12,
wed_12,
thu_12,
fri_12,
Sat_12,
Sun_12+Mon_12+Tue_12+wed_12+thu_12+fri_12+sat_12 as week_12_total,
Sun_13,
Mon_13,
Tue_13,
wed_13,
thu_13,
fri_13,
Sat_13,
Sun_13+Mon_13+Tue_13+wed_13+thu_13+fri_13+sat_13 as week_13_total,
Sun_14,
Mon_14,
Tue_14,
wed_14,
thu_14,
fri_14,
Sat_14,
Sun_14+Mon_14+Tue_14+wed_14+thu_14+fri_14+sat_14 as week_14_total,
Sun_15,
Mon_15,
Sun_15+Mon_15 as week_15_total
from (
SELECT inv_product_type,
customer_id,
patient_name,
nvl(SUM(
CASE
WHEN iwd = TRUNC(invoice_date, 'mm')
THEN
CASE WHEN iwd = invoice_date THEN 1 ELSE 0 END
END
),0)
sun_1,
nvl(SUM(
CASE
WHEN iwd + 1 >= TRUNC(invoice_date, 'mm')
THEN
CASE WHEN iwd + 1 = invoice_date THEN 1 ELSE 0 END
END
),0)
mon_1,
nvl(SUM(
CASE
WHEN iwd + 2 >= TRUNC(invoice_date, 'mm')
THEN
CASE WHEN iwd + 2 = invoice_date THEN 1 ELSE 0 END
END
),0)
tue_1,
nvl(SUM(
CASE
WHEN iwd + 3 >= TRUNC(invoice_date, 'mm')
THEN
CASE WHEN iwd + 3 = invoice_date THEN 1 ELSE 0 END
END
),0)
wed_1,
nvl(SUM(
CASE
WHEN iwd + 4 >= TRUNC(invoice_date, 'mm')
THEN
CASE WHEN iwd + 4 = invoice_date THEN 1 ELSE 0 END
END
),0)
thu_1,
nvl(SUM(
CASE
WHEN iwd + 5 >= TRUNC(invoice_date, 'mm')
THEN
CASE WHEN iwd + 5 = invoice_date THEN 1 ELSE 0 END
END
),0)
fri_1,
NVL(SUM(CASE WHEN iwd + 6 = invoice_date THEN 1 ELSE 0 END), 0) sat_1,
NVL(SUM(CASE WHEN iwd + 7 = invoice_date THEN 1 ELSE 0 END), 0) sun_2,
NVL(SUM(CASE WHEN iwd + 8 = invoice_date THEN 1 ELSE 0 END), 0) mon_2,
NVL(SUM(CASE WHEN iwd + 9 = invoice_date THEN 1 ELSE 0 END), 0) tue_2,
NVL(SUM(CASE WHEN iwd + 10 = invoice_date THEN 1 ELSE 0 END), 0) wed_2,
NVL(SUM(CASE WHEN iwd + 11 = invoice_date THEN 1 ELSE 0 END), 0) thu_2,
NVL(SUM(CASE WHEN iwd + 12 = invoice_date THEN 1 ELSE 0 END), 0) fri_2,
NVL(SUM(CASE WHEN iwd + 13 = invoice_date THEN 1 ELSE 0 END), 0) sat_2,
NVL(SUM(CASE WHEN iwd + 14 = invoice_date THEN 1 ELSE 0 END), 0) sun_3,
NVL(SUM(CASE WHEN iwd + 15 = invoice_date THEN 1 ELSE 0 END), 0) mon_3,
NVL(SUM(CASE WHEN iwd + 16 = invoice_date THEN 1 ELSE 0 END), 0) tue_3,
NVL(SUM(CASE WHEN iwd + 17 = invoice_date THEN 1 ELSE 0 END), 0) wed_3,
NVL(SUM(CASE WHEN iwd + 18 = invoice_date THEN 1 ELSE 0 END), 0) thu_3,
NVL(SUM(CASE WHEN iwd + 19 = invoice_date THEN 1 ELSE 0 END), 0) fri_3,
NVL(SUM(CASE WHEN iwd + 20 = invoice_date THEN 1 ELSE 0 END), 0) sat_3,
NVL(SUM(CASE WHEN iwd + 21 = invoice_date THEN 1 ELSE 0 END), 0) sun_4,
NVL(SUM(CASE WHEN iwd + 22 = invoice_date THEN 1 ELSE 0 END), 0) mon_4,
NVL(SUM(CASE WHEN iwd + 23 = invoice_date THEN 1 ELSE 0 END), 0) tue_4,
NVL(SUM(CASE WHEN iwd + 24 = invoice_date THEN 1 ELSE 0 END), 0) wed_4,
NVL(SUM(CASE WHEN iwd + 25 = invoice_date THEN 1 ELSE 0 END), 0) thu_4,
NVL(SUM(CASE WHEN iwd + 26 = invoice_date THEN 1 ELSE 0 END), 0) fri_4,
NVL(SUM(CASE WHEN iwd + 27 = invoice_date THEN 1 ELSE 0 END), 0) sat_4,
NVL(SUM(CASE WHEN iwd + 28 = invoice_date THEN 1 ELSE 0 END), 0) sun_5,
NVL(SUM(CASE WHEN iwd + 29 = invoice_date THEN 1 ELSE 0 END), 0) mon_5,
NVL(SUM(CASE WHEN iwd + 30 = invoice_date THEN 1 ELSE 0 END), 0) tue_5,
NVL(SUM(CASE WHEN iwd + 31 = invoice_date THEN 1 ELSE 0 END), 0) wed_5,
NVL(SUM(CASE WHEN iwd + 32 = invoice_date THEN 1 ELSE 0 END), 0) thu_5,
NVL(SUM(CASE WHEN iwd + 33 = invoice_date THEN 1 ELSE 0 END), 0) fri_5,
NVL(SUM(CASE WHEN iwd + 34 = invoice_date THEN 1 ELSE 0 END), 0) sat_5,
NVL(SUM(CASE WHEN iwd + 35 = invoice_date THEN 1 ELSE 0 END), 0) sun_6,
NVL(SUM(CASE WHEN iwd + 36 = invoice_date THEN 1 ELSE 0 END), 0) mon_6,
NVL(SUM(CASE WHEN iwd + 37 = invoice_date THEN 1 ELSE 0 END), 0) tue_6,
NVL(SUM(CASE WHEN iwd + 38 = invoice_date THEN 1 ELSE 0 END), 0) wed_6,
NVL(SUM(CASE WHEN iwd + 39 = invoice_date THEN 1 ELSE 0 END), 0) thu_6,
NVL(SUM(CASE WHEN iwd + 40 = invoice_date THEN 1 ELSE 0 END), 0) fri_6,
NVL(SUM(CASE WHEN iwd + 41 = invoice_date THEN 1 ELSE 0 END), 0) sat_6,
NVL(SUM(CASE WHEN iwd + 42 = invoice_date THEN 1 ELSE 0 END), 0) sun_7,
NVL(SUM(CASE WHEN iwd + 43 = invoice_date THEN 1 ELSE 0 END), 0) mon_7,
NVL(SUM(CASE WHEN iwd + 44 = invoice_date THEN 1 ELSE 0 END), 0) tue_7,
NVL(SUM(CASE WHEN iwd + 45 = invoice_date THEN 1 ELSE 0 END), 0) wed_7,
NVL(SUM(CASE WHEN iwd + 46 = invoice_date THEN 1 ELSE 0 END), 0) thu_7,
NVL(SUM(CASE WHEN iwd + 47 = invoice_date THEN 1 ELSE 0 END), 0) fri_7,
NVL(SUM(CASE WHEN iwd + 48 = invoice_date THEN 1 ELSE 0 END), 0) sat_7,
NVL(SUM(CASE WHEN iwd + 49 = invoice_date THEN 1 ELSE 0 END), 0) sun_8,
NVL(SUM(CASE WHEN iwd + 50 = invoice_date THEN 1 ELSE 0 END), 0) mon_8,
NVL(SUM(CASE WHEN iwd + 51 = invoice_date THEN 1 ELSE 0 END), 0) tue_8,
NVL(SUM(CASE WHEN iwd + 52 = invoice_date THEN 1 ELSE 0 END), 0) wed_8,
NVL(SUM(CASE WHEN iwd + 53 = invoice_date THEN 1 ELSE 0 END), 0) thu_8,
NVL(SUM(CASE WHEN iwd + 54 = invoice_date THEN 1 ELSE 0 END), 0) fri_8,
NVL(SUM(CASE WHEN iwd + 55 = invoice_date THEN 1 ELSE 0 END), 0) sat_8,
NVL(SUM(CASE WHEN iwd + 56 = invoice_date THEN 1 ELSE 0 END), 0) sun_9,
NVL(SUM(CASE WHEN iwd + 57 = invoice_date THEN 1 ELSE 0 END), 0) mon_9,
NVL(SUM(CASE WHEN iwd + 58 = invoice_date THEN 1 ELSE 0 END), 0) tue_9,
NVL(SUM(CASE WHEN iwd + 59 = invoice_date THEN 1 ELSE 0 END), 0) wed_9,
NVL(SUM(CASE WHEN iwd + 60 = invoice_date THEN 1 ELSE 0 END), 0) thu_9,
NVL(SUM(CASE WHEN iwd + 61 = invoice_date THEN 1 ELSE 0 END), 0) fri_9,
NVL(SUM(CASE WHEN iwd + 62 = invoice_date THEN 1 ELSE 0 END), 0) sat_9,
NVL(SUM(CASE WHEN iwd + 63 = invoice_date THEN 1 ELSE 0 END), 0) sun_10,
NVL(SUM(CASE WHEN iwd + 64 = invoice_date THEN 1 ELSE 0 END), 0) mon_10,
NVL(SUM(CASE WHEN iwd + 65 = invoice_date THEN 1 ELSE 0 END), 0) tue_10,
NVL(SUM(CASE WHEN iwd + 66 = invoice_date THEN 1 ELSE 0 END), 0) wed_10,
NVL(SUM(CASE WHEN iwd + 67 = invoice_date THEN 1 ELSE 0 END), 0) thu_10,
NVL(SUM(CASE WHEN iwd + 68 = invoice_date THEN 1 ELSE 0 END), 0) fri_10,
NVL(SUM(CASE WHEN iwd + 69 = invoice_date THEN 1 ELSE 0 END), 0) sat_10,
NVL(SUM(CASE WHEN iwd + 70 = invoice_date THEN 1 ELSE 0 END), 0) sun_11,
NVL(SUM(CASE WHEN iwd + 71 = invoice_date THEN 1 ELSE 0 END), 0) mon_11,
NVL(SUM(CASE WHEN iwd + 72 = invoice_date THEN 1 ELSE 0 END), 0) tue_11,
NVL(SUM(CASE WHEN iwd + 73 = invoice_date THEN 1 ELSE 0 END), 0) wed_11,
NVL(SUM(CASE WHEN iwd + 74 = invoice_date THEN 1 ELSE 0 END), 0) thu_11,
NVL(SUM(CASE WHEN iwd + 75 = invoice_date THEN 1 ELSE 0 END), 0) fri_11,
NVL(SUM(CASE WHEN iwd + 76 = invoice_date THEN 1 ELSE 0 END), 0) sat_11,
NVL(SUM(CASE WHEN iwd + 77 = invoice_date THEN 1 ELSE 0 END), 0) sun_12,
NVL(SUM(CASE WHEN iwd + 78 = invoice_date THEN 1 ELSE 0 END), 0) mon_12,
NVL(SUM(CASE WHEN iwd + 79 = invoice_date THEN 1 ELSE 0 END), 0) tue_12,
NVL(SUM(CASE WHEN iwd + 80 = invoice_date THEN 1 ELSE 0 END), 0) wed_12,
NVL(SUM(CASE WHEN iwd + 81 = invoice_date THEN 1 ELSE 0 END), 0) thu_12,
NVL(SUM(CASE WHEN iwd + 82 = invoice_date THEN 1 ELSE 0 END), 0) fri_12,
NVL(SUM(CASE WHEN iwd + 83 = invoice_date THEN 1 ELSE 0 END), 0) sat_12,
NVL(SUM(CASE WHEN iwd + 84 = invoice_date THEN 1 ELSE 0 END), 0) sun_13,
NVL(SUM(CASE WHEN iwd + 85 = invoice_date THEN 1 ELSE 0 END), 0) mon_13,
NVL(SUM(CASE WHEN iwd + 86 = invoice_date THEN 1 ELSE 0 END), 0) tue_13,
NVL(SUM(CASE WHEN iwd + 87 = invoice_date THEN 1 ELSE 0 END), 0) wed_13,
NVL(SUM(CASE WHEN iwd + 88 = invoice_date THEN 1 ELSE 0 END), 0) thu_13,
NVL(SUM(CASE WHEN iwd + 89 = invoice_date THEN 1 ELSE 0 END), 0) fri_13,
NVL(SUM(CASE WHEN iwd + 90 = invoice_date THEN 1 ELSE 0 END), 0) sat_13,
NVL(SUM(CASE WHEN iwd + 91 = invoice_date THEN 1 ELSE 0 END), 0) sun_14,
NVL(SUM(CASE WHEN iwd + 92 = invoice_date THEN 1 ELSE 0 END), 0) mon_14,
NVL(SUM(CASE WHEN iwd + 93 = invoice_date THEN 1 ELSE 0 END), 0) tue_14,
NVL(SUM(CASE WHEN iwd + 94 = invoice_date THEN 1 ELSE 0 END), 0) wed_14,
NVL(SUM(CASE WHEN iwd + 95 = invoice_date THEN 1 ELSE 0 END), 0) thu_14,
NVL(SUM(CASE WHEN iwd + 96 = invoice_date THEN 1 ELSE 0 END), 0) fri_14,
NVL(SUM(CASE WHEN iwd + 97 = invoice_date THEN 1 ELSE 0 END), 0) sat_14,
NVL(SUM(CASE WHEN iwd + 98 = invoice_date THEN 1 ELSE 0 END), 0) sun_15,
NVL(SUM(CASE WHEN iwd + 99 = invoice_date THEN 1 ELSE 0 END), 0) mon_15
FROM (SELECT TO_CHAR(TRUNC(ih.invoice_date, 'mm'), 'yyyymm') ym,
NEXT_DAY(TRUNC(TO_DATE(date_from, 'DD-MON-YYYY'), 'mm'), 'Sun') iwd,
CASE
WHEN vp.inv_product_type = 'RBC' THEN 'Red Blood Cells'
WHEN vp.inv_product_type = 'LRBC' THEN 'Leukoreduced Red Blood Cells'
WHEN vp.inv_product_type = 'LPHER' THEN 'SDP'
WHEN vp.inv_product_type = 'PHER' THEN 'SDP'
WHEN vp.inv_product_type = 'FFP' THEN 'Fresh Frozen Plasma'
WHEN vp.inv_product_type = 'FP24' THEN 'Plasma Frozen withing 24hrs'
WHEN vp.inv_product_type = 'CRYO' THEN 'Cryoprecipitate'
END
inv_product_type,
ih.customer_id,
id.patient_name,
ih.invoice_date
FROM tab1 ih,
tab2 id,
tab4 ip,
tab3 vp
WHERE id.invoice_number = ih.invoice_number
AND id.item_id = vp.product_code
AND id.item_id = ip.item_id
AND ip.item_type IN ('P')
-- AND ih.customer_id = 'WAD-EX0128'
AND ih.invoice_date >= TO_DATE(ym, 'YYYYMM')
AND ih.invoice_date < ADD_MONTHS(TO_DATE(ym, 'YYYYMM'), 3)
--AND ih.invoice_date = TO_DATE(ym, 'YYYYMM')
AND vp.inv_product_type IN ('RBC',
'LRBC',
'LPHER',
'PHER',
'FFP',
'FP24',
'CRYO'))
GROUP BY inv_product_type, customer_id, patient_name
ORDER BY inv_product_type, customer_id, patient_name
);
varc1 c1%ROWTYPE;
BEGIN
SELECT REPLACE(days, ',', CHR(9)) data
INTO v_header
FROM ( SELECT SUBSTR(
SYS_CONNECT_BY_PATH(
CASE MOD(LEVEL,8) -- every 8e column
WHEN 0 THEN
TO_CHAR(
NEXT_DAY(TO_DATE(ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1
- TRUNC(LEVEL/8) -1, -- 1 day less then level for each 8 values sets its back to the week of level 7
null--'WW-YYYY'
)
ELSE
TO_CHAR(
NEXT_DAY(TO_DATE(ym, 'YYYYMM') - 1, 'Sun') + LEVEL - 1
- TRUNC(LEVEL/8), -- 1 day less then level for each 8 values (else 1 day will be skipped each week
'dd-Mon-yyyy'
) END,
','
),
2
)
days
FROM DUAL
CONNECT BY LEVEL <= 12 * 8
ORDER BY LEVEL DESC)
WHERE ROWNUM = 1;
-- DBMS_OUTPUT.put_line(v_header);
v_location_i := 1000;
--FILENAME1 := 'hosp_usage' || '_' || SYSDATE || '.CSV';
filename1 := 'hosp_usage' || '_' || SYSDATE || '.xls';
v_location_i := 2000;
filename := UTL_FILE.fopen('HOSP_USAGE_DIR', filename1, 'W',max_linesize => 32767);
/* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
v_location_i := 3000;
UTL_FILE.put_line(
filename,
'INV PRODUCT TYPE' || CHR(9) || 'CUSTOMER ID' || CHR(9) || 'NAME' || CHR(9) || v_header
);
OPEN c1;
v_location_i := 4000;
LOOP
FETCH c1 INTO varc1;
v_location_i := 5000;
EXIT WHEN c1%NOTFOUND;
/* THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
v_location_i := 6000;
UTL_FILE.put_line(
filename,
varc1.inv_product_type
|| CHR(9)
|| varc1.customer_id
|| CHR(9)
|| varc1.patient_name
|| CHR(9)
|| varc1.sun_1
|| CHR(9)
|| varc1.mon_1
|| CHR(9)
|| varc1.tue_1
|| CHR(9)
|| varc1.wed_1
|| CHR(9)
|| varc1.thu_1
|| CHR(9)
|| varc1.fri_1
|| CHR(9)
|| varc1.sat_1
|| CHR(9)
|| varc1.week_1_total
|| CHR(9)
|| varc1.sun_2
|| CHR(9)
|| varc1.mon_2
|| CHR(9)
|| varc1.tue_2
|| CHR(9)
|| varc1.wed_2
|| CHR(9)
|| varc1.thu_2
|| CHR(9)
|| varc1.fri_2
|| CHR(9)
|| varc1.sat_2
|| CHR(9)
|| varc1.week_2_total
|| CHR(9)
|| varc1.sun_3
|| CHR(9)
|| varc1.mon_3
|| CHR(9)
|| varc1.tue_3
|| CHR(9)
|| varc1.wed_3
|| CHR(9)
|| varc1.thu_3
|| CHR(9)
|| varc1.fri_3
|| CHR(9)
|| varc1.sat_3
|| CHR(9)
|| varc1.week_3_total
|| CHR(9)
|| varc1.sun_4
|| CHR(9)
|| varc1.mon_4
|| CHR(9)
|| varc1.tue_4
|| CHR(9)
|| varc1.wed_4
|| CHR(9)
|| varc1.thu_4
|| CHR(9)
|| varc1.fri_4
|| CHR(9)
|| varc1.sat_4
|| CHR(9)
|| varc1.week_4_total
|| CHR(9)
|| varc1.sun_5
|| CHR(9)
|| varc1.mon_5
|| CHR(9)
|| varc1.tue_5
|| CHR(9)
|| varc1.wed_5
|| CHR(9)
|| varc1.thu_5
|| CHR(9)
|| varc1.fri_5
|| CHR(9)
|| varc1.sat_5
|| CHR(9)
|| varc1.week_5_total
|| CHR(9)
|| varc1.sun_6
|| CHR(9)
|| varc1.mon_6
|| CHR(9)
|| varc1.tue_6
|| CHR(9)
|| varc1.wed_6
|| CHR(9)
|| varc1.thu_6
|| CHR(9)
|| varc1.fri_6
|| CHR(9)
|| varc1.sat_6
|| CHR(9)
|| varc1.week_6_total
|| CHR(9)
|| varc1.sun_7
|| CHR(9)
|| varc1.mon_7
|| CHR(9)
|| varc1.tue_7
|| CHR(9)
|| varc1.wed_7
|| CHR(9)
|| varc1.thu_7
|| CHR(9)
|| varc1.fri_7
|| CHR(9)
|| varc1.sat_7
|| CHR(9)
|| varc1.week_7_total
|| CHR(9)
|| varc1.sun_8
|| CHR(9)
|| varc1.mon_8
|| CHR(9)
|| varc1.tue_8
|| CHR(9)
|| varc1.wed_8
|| CHR(9)
|| varc1.thu_8
|| CHR(9)
|| varc1.fri_8
|| CHR(9)
|| varc1.sat_8
|| CHR(9)
|| varc1.week_8_total
|| CHR(9)
|| varc1.sun_9
|| CHR(9)
|| varc1.mon_9
|| CHR(9)
|| varc1.tue_9
|| CHR(9)
|| varc1.wed_9
|| CHR(9)
|| varc1.thu_9
|| CHR(9)
|| varc1.fri_9
|| CHR(9)
|| varc1.sat_9
|| CHR(9)
|| varc1.week_9_total
|| CHR(9)
|| varc1.sun_10
|| CHR(9)
|| varc1.mon_10
|| CHR(9)
|| varc1.tue_10
|| CHR(9)
|| varc1.wed_10
|| CHR(9)
|| varc1.thu_10
|| CHR(9)
|| varc1.fri_10
|| CHR(9)
|| varc1.sat_10
|| CHR(9)|| varc1.week_10_total
|| CHR(9)
|| varc1.sun_11
|| CHR(9)
|| varc1.mon_11
|| CHR(9)
|| varc1.tue_11
|| CHR(9)
|| varc1.wed_11
|| CHR(9)
|| varc1.thu_11
|| CHR(9)
|| varc1.fri_11
|| CHR(9)
|| varc1.sat_11
|| CHR(9)
|| varc1.week_11_total
|| CHR(9)
|| varc1.sun_12
|| CHR(9)
|| varc1.mon_12
|| CHR(9)
|| varc1.tue_12
|| CHR(9)
|| varc1.wed_12
|| CHR(9)
|| varc1.thu_12
|| CHR(9)
|| varc1.fri_12
|| CHR(9)
|| varc1.sat_12
|| CHR(9)
|| varc1.week_12_total
|| CHR(9)
|| varc1.sun_13
|| CHR(9)
|| varc1.mon_13
|| CHR(9)
|| varc1.tue_13
|| CHR(9)
|| varc1.wed_13
|| CHR(9)
|| varc1.thu_13
|| CHR(9)
|| varc1.fri_13
|| CHR(9)
|| varc1.sat_13
|| CHR(9)
|| varc1.week_13_total
|| CHR(9)
|| varc1.sun_14
|| CHR(9)
|| varc1.mon_14
|| CHR(9)
|| varc1.tue_14
|| CHR(9)
|| varc1.wed_14
|| CHR(9)
|| varc1.thu_14
|| CHR(9)
|| varc1.fri_14
|| CHR(9)
|| varc1.sat_14
|| CHR(9)
|| varc1.week_14_total
|| CHR(9)
|| varc1.sun_15
|| CHR(9)
|| varc1.mon_15
|| CHR(9)
|| varc1.week_15_total
);
END LOOP;
v_location_i := 6000;
v_location_i := 7000;
UTL_FILE.fclose(filename);
v_location_i := 8000;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Error in ' || v_procedure_c || ' at line: ' || v_location_i);
END load_hosp_usage_05072015;
/
Header is restricted to 12 weeks. Data goes beyond 12 weeks. Header stops at 28-mar-2015. How can I restrict data for 12 weeks? Can I use CONNECT BY LEVEL <= 12 and if so where should the condition be?
header-and-data.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dont worry I got it.
ASKER
Thanks
ASKER
Open in new window
I am getting an error. I think paste is not correct on my side.
ORA-00936: missing expression
sample-date.txt