When you select records why is it that if you do not include all the fields in the group by statement you get an error not a group by statement? In this case l only want to group by brn386,bro386,dea386.
The following error has occurred:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-00979: not a GROUP BY expression
ORA-06512: at "SLEEZY.MIS_PERSAL_PROC_BSP", line 66
ORA-06512: at line 9
CREATE OR REPLACE PROCEDURE MIS_PERSAL_PROC_bsp
(v_start_date DATE, v_end_date DATE)
IS
v_cnt NUMBER;
BEGIN
------------------------------------------------------------------- Brokers --
-- BSPs ----------------------------------------------------------------------
-- BSPs no. 1 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl,institution,summary_date,change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da1386,(cap386*1.4),COUNT(da1386),AH1386,SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah1386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,dea386,bro386,lof386,cap386,AH1386,da1386);
-- BSPs no. 2 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl,institution,summary_date,change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da2386,COUNT(da2386),(cap386*1.4),AH2386,SYSDATE,v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah2386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,dea386,bro386,lof386,cap386,AH2386,da2386);
-- BSPs no. 3 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl, institution,summary_date, change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da3386,COUNT(da3386),(cap386*1.4),AH3386,SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah3386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH3386,da3386);
-- BSPs no. 4 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl,institution,summary_date,change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da4386,COUNT(da4386),(cap386*1.4),AH4386,SYSDATE,v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah4386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH4386,da4386);
-- BSPs no. 5 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanoff,capital,total,loan_count,Pdincl, institution,summary_date, change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da5386,COUNT(da5386),(cap386*1.4),AH5386,SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah5386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH5386,da5386);
-- BSPs no. 6 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl,institution,summary_date,change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da6386,COUNT(da6386),(cap386*1.4),AH6386,SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah6386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH6386);
-- BSPs no. 7 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl, institution,summary_date,change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da7386,COUNT(da7386),(cap386*1.4),AH7386,SYSDATE,v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah7386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH7386,da7386);
-- BSPs no. 8 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl,institution,summary_date,change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da8386,COUNT(da8386),(cap386*1.4),AH8386,SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah8386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH8386,da8386);
-- BSPs no. 9 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl, institution,summary_date, change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da9386,COUNT(da9386),(cap386*1.4),AH9386,SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah9386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH9386,da9386);
-- BSPs no. 0 --
INSERT INTO MIS_PERSAL
(branch,brocker,dealref,loanOff,capital,total,loan_count,Pdincl,institution,summary_date,change_date)
(SELECT brn386,bro386,dea386,lof386,cap386,da0386,COUNT(da0386),(cap386*1.4),AH0386,SYSDATE, v_start_date
FROM PW38600P
WHERE bro386 NOT IN ('SBSA',
'KIOSKS',
'HMC',
'CREDIT',
'ESP',
'PSA',
'AIN',
'HOSPERSA',
'REGIONAL',
'COVERHOLD',
'WACC',
'NATU',
'ACC CARE',
'AB CC'
)
AND lof386 NOT IN
('153', '1114', '97', '4843', '4849', '1157', 'E0080', 'J0196','15121')
AND ah0386 LIKE '%AFRICAN BANK%' AND DEA386 != 'EDA002216'
AND did386 BETWEEN v_start_date AND v_end_date
GROUP BY brn386,bro386,dea386,cap386,AH0386,da0386);
----------------------------------------------------------------
--- 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,Pdincl,change_date,summary_date)
SELECT branch,brocker,sbrockercd,Total,loan_count,Pdincl,change_date,summary_date
FROM MIS_PERSAL;
END IF;
COMMIT;
END;
/
|
] 
Blog
Search
Help
Members
Register
Login
Home
