I have this SP, where am trying to insert records in an Oracle target table(11.2). Here in the subquery naming as 'a' where i have a column PROJECT which is one of the joining condition for the subquery 'b' but here am not getting the desired result if i put the column 'project' in the group by clause in subquery 'a'. But at same time i need to refer the same as one of the joining conditions of subquery 'b'
What I have tried:
create or replace PROCEDURE PROC1 AS
BEGIN
INSERT INTO TARGET TABLE
SELECT
a.SECT,
a. REG,
a.USRN,
a.PRO_CT,
a.CATG,
a.TLT,
a.SN_YR_MN,
a.SN_YR,
a.SN_MN,
a.UNIQUE_ISSUES_CNT,
a.FILES_SN_ISS_CT,
b.TOT_FILES_SN_CT,
b.TOT_SN_CT,
CURRENT_DATE
FROM(select SECT, REG,USRN,PROJECT,count(DISTINCT concat(PROJECT,SOLUTION)) PRO_CT,CATG,TLT,SN_YR_MN,SN_YR,SN_MN,
count(DISTINCT ISSUE_ID) UNIQUE_ISSUES_CNT,count(DISTINCT PATH)FILES_SN_ISS_CT
FROM table1
GROUP BY SECT, REG,PROJECT,USRN,CATG,TLT,SN_YR_MN,SN_YR,SN_MN)a
JOIN
(
SELECT USRN,
PROJECT,
SECT,
REG,
SN_YR_MN,
SN_YR,
SN_MN,
count(DISTINCT RPT_SCAN_SUMMARY.PATH) TOT_FILES_SN_CT,
count(DISTINCT RPT_SCAN_SUMMARY.SCAN_ID) TOT_SN_CT
FROM table2
GROUP BY USRN,
PROJECT,
SECT,
REG,
SN_YR_MN,
SN_YR,
PROJECT,
SN_MN) b ON a.USRN = b.USRN AND a.SECT = b.SECT AND
a. REG = b. REG AND a.SN_YR_MN = b.SN_YR_MN AND a.SN_YR= b.SN_YR AND a.SN_MN= b.SN_MN AND a.PROJECT=b.PROJECT;