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 Go to next message
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;
Re: Encountering issues with join condition in stored procedure [message #670289 is a reply to message #670288] Sat, 23 June 2018 03:10 Go to previous messageGo to next message
John Watson
Messages: 8075
Registered: January 2010
Location: Global Village
Senior Member
Please use format your code with some sort of code formatter, here's one,
http://www.dpriver.com/pp/sqlformat.htm
and then use [code] tags to make it readable. You have been shown how to do this before.
Re: Encountering issues with join condition in stored procedure [message #670290 is a reply to message #670289] Sat, 23 June 2018 06:33 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4154289
Re: Encountering issues with join condition in stored procedure [message #670291 is a reply to message #670290] Sat, 23 June 2018 09:46 Go to previous messageGo to next message
senmng
Messages: 22
Registered: April 2018
Junior Member
HI - Yes, i tried with some different logic and thought it got fixed but still am not able to get the expected result. Apologize for that.
Re: Encountering issues with join condition in stored procedure [message #670292 is a reply to message #670291] Sat, 23 June 2018 18:01 Go to previous message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Previous Topic: join is slow
Next Topic: Oracle Text index column when searching multiple tables
Goto Forum:
  


Current Time: Sun Dec 08 15:16:07 CST 2019