Home » RDBMS Server » Performance Tuning » Encountering issues with join condition in stored procedure (Oracle 11.2)
| Encountering issues with join condition in stored procedure [message #670288] |
Fri, 22 June 2018 23:45  |
 |
senmng
Messages: 22
Registered: April 2018
|
Junior Member |
|
|
Hi - Here am trying to insert a target table using this stored procedure but am getting incorrect values for two columns-
st_cnt.COUNT_FILES,st_cnt.COUNT_SCANS
where we are getting values for these columns from the join st_cnt where the query results are grouped by PROJ,SOL,SCAN_YEAR,SCAN_MONTH columns.
When i tried to run as a individual query(from st_cnt) ,we are getting the expected output but while running as whole we are getting huge row count.
INSERT INTO TGT_TABLE
SELECT table1.DA_SEC,
table1.DA_REG,
table1.PROJ,
table1.SOL,
table1.SCAN_YEAR_MON,
to_char(TO_DATE(table1.SCAN_YEAR_MON,'YYYY-MM'),'YYYY'),
to_char(TO_DATE(table1.SCAN_YEAR_MON,'YYYY-MM'),'MM'),
COUNT(DISTINCT table1.ISSUE_ID),
COUNT(DISTINCT table1.USERNAME),
COUNT(DISTINCT table1.PATH),
st_cnt.COUNT_FILES,
st_cnt.COUNT_SCANS,
sum(unq_Pro.HIGH_CNT),
sum(unq_Pro.MEDIUM_CNT),
sum(unq_Pro.LOW_CNT),
sum(SUPPR_HIGH) - sum(UNSUPPR_HIGH),
sum(SUPPR_MEDIUM) - sum(UNSUPPR_MEDIUM),
sum(SUPPR_LOW) - sum(UNSUPPR_LOW),
CURRENT_DATE
FROM table1
JOIN
(SELECT PROJ,SOL,
SUM(CASE WHEN ISSUE_IMP = 'high' THEN 1 ELSE 0 END) SUPPR_HIGH,
SUM(CASE WHEN ISSUE_IMP = 'medium' THEN 1 ELSE 0 END) SUPPR_MEDIUM,
SUM(CASE WHEN ISSUE_IMP = 'low' THEN 1 ELSE 0 END) SUPPR_LOW
from table1
LEFT OUTER JOIN
SUPPR_ISSUE@DATABASE_LINK1
ON table1.ISSUE_ID = SUPPR_ISSUE.ISSUE_ID
GROUP BY PROJ,SOL
) SUPPR
ON table1.PROJ = SUPPR.PROJ
JOIN
(SELECT PROJ,SOL,
SUM(CASE WHEN ISSUE_IMP = 'high' THEN 1 ELSE 0 END) UNSUPPR_HIGH,
SUM(CASE WHEN ISSUE_IMP = 'medium' THEN 1 ELSE 0 END) UNSUPPR_MEDIUM,
SUM(CASE WHEN ISSUE_IMP = 'low' THEN 1 ELSE 0 END) UNSUPPR_LOW
FROM table1
LEFT OUTER JOIN
UNSUPPR_ISSUE@DATABASE_LINK1
ON table1.ISSUE_ID = UNSUPPR_ISSUE.ISSUE_ID
GROUP BY PROJ,SOL
) UNSUPPR
ON table1.PROJ = UNSUPPR.PROJ
) t1
JOIN
(SELECT PROJ,SOL,SCAN_YEAR,SCAN_MONTH,COUNT(DISTINCT PATH) COUNT_FILES,COUNT(DISTINCT SCAN_ID) COUNT_SCANS
FROM table2
GROUP BY PROJ,SOL,SCAN_YEAR,SCAN_MONTH
) st_cnt
ON st_cnt.PROJ = table1.PROJ --AND st_cnt.SOL = table1.SOL
(SELECT
unq_iss.PROJ,
unq_iss.SOL,
unq_iss.SCAN_YEAR_MON,
sum(CASE WHEN unq_iss.ISSUE_IMP = 'high' THEN 1 ELSE 0 END) HIGH_CNT,
sum(CASE WHEN unq_iss.ISSUE_IMP = 'medium' THEN 1 ELSE 0 END) MEDIUM_CNT,
sum(CASE WHEN unq_iss.ISSUE_IMP = 'low' THEN 1 ELSE 0 END) LOW_CNT
FROM (SELECT DISTINCT
table1.ISSUE_ID,
table1.ISSUE_IMP,
table1.PROJ,
table1.SOL,
table1.SCAN_YEAR_MON
FROM table1
) unq_iss
GROUP BY unq_iss.PROJ, unq_iss.SOL, unq_iss.SCAN_YEAR_MON
) unq_Pro
ON unq_Pro.PROJ = t1.PROJ
AND unq_Pro.SCAN_YEAR_MON = t1.SCAN_YEAR_MON
GROUP BY table1.DA_SEC,table1.DA_REG,table1.PROJ,table1.SOL,table1.SCAN_YEAR_MON, st_cnt.COUNT_FILES,st_cnt.COUNT_SCAN;
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 15:33:52 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
