| Cursors [message #19293] |
Sat, 09 March 2002 15:19  |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
How do l declare three cursors in this proc? keep getting errors how do l fix that? Is my syntax correct? l want to insert
into. The original script is
-- BSPs no. 1 --
INSERT INTO MIS_PERSAL
(branch, total, loan_count, summary_date, change_date)
(SELECT brn386, SUM (da1386), COUNT (da1386), SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND ah1386 NOT LIKE '%AFRICAN BANK%'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386);
-- BSPs no. 2 --
INSERT INTO MIS_PERSAL
(branch, total, loan_count, summary_date, change_date)
(SELECT brn386, SUM (da2386), COUNT (da2386), SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196')
AND ah2386 NOT LIKE '%AFRICAN BANK%'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386);
bsp3 which will be ah3386 and da3386 up to 10.
Create or Replace Procedure ExtractFrom_Wh_Proc
(v_start_end date,v_end_date date)
IS
BEGIN
-- Bsp Cursor
DECLARE
CURSOR BSP_CUR
IS
SELECT DEA386,BRN386,BRO386,SBR386,LOF386, AH1386, DA1386,
AH2386, DA2386, AH3386, DA3386,AH4386,DA4386,AH5386,
DA5386,AH6386,DA6386,AH7386,DA7386,AH8386,DA8386,AH9386,
DA9386,AH0386,DA0386,DID386
FROM PW38600P
Where BRO386 NOT IN ('SBSA','KIOSKS','HMC','CREDIT','ESP','PSA','AIN',
'HOSPERSA','REGIONAL','COVERHOLD','WACC','NATU',
'ACC CARE','TAF BROK','AB CC'
)
AND LOF386 NOT IN ('153','1114','97','4843','4849',
'1157','E0080','J0196','15121'
)
(AND AH1386 LIKE 'AFRICAN BANK' AND AH1386 LIKE 'AFRICAN BANK'
AND AH1386 LIKE 'AFRICAN BANK' AND AH1386 LIKE 'AFRICAN BANK'
AND AH1386 LIKE 'AFRICAN BANK' AND AH1386 LIKE 'AFRICAN BANK'
AND AH1386 LIKE 'AFRICAN BANK' AND AH1386 LIKE 'AFRICAN BANK'
AND AH1386 LIKE 'AFRICAN BANK' AND AH1386 LIKE 'AFRICAN BANK'
AND AH1386 LIKE 'AFRICAN BANK' AND AH1386 LIKE 'AFRICAN BANK')
AND DEA386 != 'EDA002216' AND DEA386 ='4000000633'
AND DID386 BETWEEN v_start_date AND v_end_date
GROUP BY BRN386, BRO386, DEA386,CAP386);
BSP_REC BSP_CUR%ROWTYPE;
BEGIN
OPEN BSP_CUR
LOOP;
FETCH BSP_CUR INTO BSP_REC;
EXIT WHEN BSP_CUR%NOTFOUND;
END LOOP;
CLOSE BSP_CUR;
COMMIT;
END;
-- Callcenter --------
-- Callcenter no. 1 --
DECLARE
CURSOR Call_CenterCur
IS
SELECT DEA386,BRN386,BRO386,SBR386,LOF386, AH1386, DA1386,
AH2386, DA2386, AH3386, DA3386,AH4386,DA4386,AH5386,
DA5386,AH6386,DA6386,AH7386,DA7386,AH8386,DA8386,AH9386,
DA9386,AH0386,DA0386,DID386
FROM PW38600P
WHERE bro386 = 'AB CC'
(AND AH1386 NOT LIKE '%AFRICAN BANK%' AND AH2386 NOT LIKE '%AFRICAN BANK%'
AND AH3386 NOT LIKE '%AFRICAN BANK%' AND AH4386 NOT LIKE '%AFRICAN BANK%'
AND AH5386 NOT LIKE '%AFRICAN BANK%' AND AH6386 NOT LIKE '%AFRICAN BANK%'
AND AH7386 NOT LIKE '%AFRICAN BANK%' AND AH8386 NOT LIKE '%AFRICAN BANK%'
AND AH9386 NOT LIKE '%AFRICAN BANK%' AND AH0386 NOT LIKE '%AFRICAN BANK%')
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY bro386,sbr386;
Call_Center_Rec Call_CenterCur%ROWTYPE;
BEGIN
OPEN Call_CenterCur
LOOP;
FETCH Call_CenterCur INTO Call_Center_Rec;
EXIT WHEN Call_CenterCur%NOTFOUND;
END LOOP;
CLOSE Call_CenterCur;
COMMIT;
END;
-- Mobile Sales ------------------------
-- Mobile Sales no. 1 ah1386 - ah0386 --
DECLARE
CURSOR MOBILE_CUR
IS
SELECT DEA386,BRN386,BRO386,SBR386,LOF386, AH1386, DA1386,
AH2386, DA2386, AH3386, DA3386,AH4386,DA4386,AH5386,
DA5386,AH6386,DA6386,AH7386,DA7386,AH8386,DA8386,AH9386,
DA9386,AH0386,DA0386,DID386
FROM PW38600P
WHERE BRO386 = 'CREDIT'
(AND AH1386 NOT LIKE '%AFRICAN BANK%' AND AH2386 NOT LIKE '%AFRICAN BANK%'
AND AH3386 NOT LIKE '%AFRICAN BANK%' AND AH4386 NOT LIKE '%AFRICAN BANK%'
AND AH5386 NOT LIKE '%AFRICAN BANK%' AND AH6386 NOT LIKE '%AFRICAN BANK%'
AND AH7386 NOT LIKE '%AFRICAN BANK%' AND AH8386 NOT LIKE '%AFRICAN BANK%'
AND AH9386 NOT LIKE '%AFRICAN BANK%' AND AH0386 NOT LIKE '%AFRICAN BANK%')
AND DID386 BETWEEN V_START_DATE AND V_END_DATE
GROUP BY BRO386, SBR386);
MOBILE_REC MOBILE_CUR%ROWTYPE;
BEGIN
OPEN MOBILE_CUR
LOOP;
FETCH MOBILE_CUR INTO MOBILE_REC;
EXIT WHEN BSP_CUR%NOTFOUND;
END LOOP;
CLOSE MOBILE_CUR;
COMMIT;
END;
----------------------------------------------------------------
--- Check for Duplicates Before Inserting into History Table --
----------------------------------------------------------------
v_cnt := 0;
SELECT COUNT (*)
INTO v_cnt
FROM MIS_PERSAL_HIST
WHERE TO_DATE(v_start_date,'yyyy-mm-dd') =
TO_DATE(change_date,'yyyy-mm-dd')
AND TO_DATE(SYSDATE,'yyyy-mm-dd') =
TO_DATE(summary_date,'yyyy-mm-dd');
IF v_cnt = 0
THEN
INSERT INTO MIS_PERSAL_HIST
(branch, brocker, sbrockercd, total, loan_count, change_date,
summary_date)
SELECT branch, brocker, sbrockercd, total, loan_count, change_date,
summary_date
FROM MIS_PERSAL;
END IF;
COMMIT;
END;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
