Home » SQL & PL/SQL » SQL & PL/SQL » Counter
| Counter [message #18975] |
Sun, 24 February 2002 09:45  |
Rm69
Messages: 39
Registered: January 2002
|
Member |
|
|
Q) Would like to build a run counter so that each time my procedure runs the v_run_no parameter will insert a run
number into the run_no column and increament by 1. Thats so that l know which run.Its not working whats
wrong with my counter???
CREATE OR REPLACE PROCEDURE mis_newbuspdincl_procWithNo
(v_start_date DATE, v_end_date DATE)
IS
v_cnt NUMBER;
v_Run_No number;
BEGIN
--------------------------------------------------------------------------------
-- OTHER ---------------------------------------------------------------------
--------------------------------------------------------------------------------
-- SBSA --
INSERT INTO mis_newbus_pdincl
(branch, sbsa, sbsa_count, summary_date, captured,run_no)
SELECT branch, SUM (pdincl), COUNT (pdincl), SYSDATE, v_start_date,v_run_no
FROM zw30800p
WHERE disdate BETWEEN v_start_date AND v_end_date
AND company IN ('002')
GROUP BY branch;
-- COMPLIANT --
INSERT INTO mis_newbus_pdincl
(sbrokercd, payroll_compliant, pay_comp_count, summary_date,
captured, run_no)
SELECT sbrokercd, SUM (pdincl), COUNT (pdincl), SYSDATE, v_start_date,v_run_no
FROM zw30800p
WHERE SUBSTR (product, 1, 1) IN ('D', 'N', 'J', 'S')
AND ( product = 'CL001'
OR product = 'CL002'
AND brokercd IN ('CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'TAF BROK'
)
AND company != '002'
OR SUBSTR (product, 1, 1) = 'T'
OR SUBSTR (product, 3, 1) = 'T'
)
AND disdate BETWEEN v_start_date AND v_end_date
GROUP BY sbrokercd;
--------------------------------------------------------------------------------
-- CHECKING FOR DUPLICATES BEFORE COPYING DATA TO HISTORY TABLE --------------
--------------------------------------------------------------------------------
v_cnt := 0;
v_Run_No :=0;
SELECT COUNT (*)
INTO v_cnt
FROM mis_newbus_pdincl_hist
WHERE TO_DATE (v_start_date, 'yyyy-mm-dd') =
TO_DATE (captured, '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_newbus_pdincl_hist
(branch, sbrokercd, sbsa, sbsa_count,catchall,
........... summary_date,run_no)
SELECT branch, sbrokercd, sbsa, sbsa_count,
captured, summary_date,run_no
FROM mis_newbus_pdincl;
END IF;
v_Run_No := v_RunNo + 1;
END;
/
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 06:33:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
