OraFAQ Forum: Performance Tuning » Appropriate join to the desired output

Home » RDBMS Server » Performance Tuning » Appropriate join to the desired output (Oracle 11.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Appropriate join to the desired output [message #670820]

Fri, 27 July 2018 18:21

senmng
Messages: 22
Registered: April 2018

Junior Member

I have this query to get the count of scan_ids(first/last/prior) from table 1 by joining with table 3 using WITH clause A,B and C. Am not able to get the desired output while trying to join the all these sub queries and the records got scattered in to many rows.

CREATE TABLE table1 (
PROJECT VARCHAR2(256 CHAR) NOT NULL,
SOLUTION VARCHAR2(256 CHAR),
FIRST_SCAN_ID NUMBER(10) NOT NULL,
LAST_SCAN_ID NUMBER(10) NOT NULL,
PRIOR_SCAN_ID NUMBER(10) NOT NULL,
CONSTRAINT pk_tmp_first_last_scan_ids_id PRIMARY KEY (PROJECT)
);
CREATE TABLE table3
(
SOA_SECTOR VARCHAR2(128),
SOA_REGION VARCHAR2(128),
USERNAME VARCHAR2(128),
PROJECT VARCHAR2(256) NOT NULL,
SOLUTION VARCHAR2(256),
PATH VARCHAR2(2048) NOT NULL,
TIME DATE NOT NULL,
LOC NUMBER(10) NOT NULL,
SCAN_ID VARCHAR2(256),
SESSION_ID VARCHAR2(256),
FILE_ID VARCHAR2(256),
FILE_INFO_ID VARCHAR2(256),
SCAN_YEAR_MON VARCHAR2(7),
SCAN_YEAR VARCHAR2(7),
SCAN_MONTH VARCHAR2(7),
LOAD_DATE_TIME DATE NOT NULL,
CONSTRAINT rpt_scan_summary_pk PRIMARY KEY (SCAN_ID, FILE_ID)
)
What i have tried

WITH A AS(select a.project,a.solution,b.SOA_SECTOR,b.SOA_REGION,count(a.first_scan_id) cnt1 from table1 a join table3 b ON b.Project =a.PROJECT AND b.scan_id=a.FIRST_SCAN_ID where a.PROJECT='test'
group by a.project,a.solution,b.SOA_SECTOR,b.SOA_REGION),
B AS(select a.project,a.solution,b.SOA_SECTOR,b.SOA_REGION,count(a.last_scan_id) cnt1 from table1 a join table3 b ON b.Project =a.PROJECT AND b.scan_id=a.LAST_SCAN_ID where a.PROJECT='test'
group by a.project,a.solution,b.SOA_SECTOR,b.SOA_REGION),
C AS(select a.project,a.solution,b.SOA_SECTOR,b.SOA_REGION,count(a.prior_scan_id) cnt1 from table1 a join table3 b ON b.Project =a.PROJECT AND b.scan_id=a.PRIOR_SCAN_ID where a.PROJECT='test'
group by a.project,a.solution,b.SOA_SECTOR,b.SOA_REGION)
SELECT A.SOA_SECTOR,
A.SOA_REGION,
A.PROJECT,
A.SOLUTION,
--A.cnt2,
--A.cnt3,
A.cnt1,
--D.COUNT_FILE,
--B.cnt2,
-- B.cnt3,
B.cnt1,
-- C.cnt2,
--C.cnt3,
C.cnt1,
CURRENT_DATE
FROM A JOIN B ON B.PROJECT=A.PROJECT AND B.SOA_SECTOR=A.SOA_SECTOR AND B.SOA_REGION=B.SOA_REGION
JOIN C ON C.PROJECT=A.PROJECT AND C.SOA_SECTOR=A.SOA_SECTOR AND C.SOA_REGION A.SOA_REGION;

Here is the query result of A,B and C when i ran separately for a particular project

Query result of subquery A

Project solution soa_sector soa_region cnt1
test ICG ASPAC 129
test GC NA 13
test GC MEX 1
test Divested/JV ASPAC 8
test ICG NA 4
test EO&T ASPAC 21
test EO&T NA 92
test GC ASPAC 7
test null null 26
test ICG EMEA 3
Query result of B

Project solution soa_sector soa_region cnt1
test ICG ASPAC 130
test GC NA 14
test EO&T EMEA 1
test GC MEX 1
test Divested/JV ASPAC 7
test ICG NA 5
test EO&T ASPAC 20
test EO&T NA 93
test GC ASPAC 7
test null null 23
test ICG EMEA 3
Query result of C

Project solution soa_sector soa_region cnt1
test ICG ASPAC 60
test GC NA 4
test EO&T EMEA 1
test Divested/JV ASPAC 2
test ICG NA 3
test EO&T ASPAC 14
test EO&T NA 25
test NUll NULL 14
test ICG EMEA 3
test GC ASPAC 4
Query result of whole SQL

soa_sector soa_region Project solution A.cnt1 B.cnt1 C.cnt1
ICG EMEA test 3 130 3
ICG N A test 4 130 3
ICG ASPAC test 129 130 60
GC ASPAC test 7 14 4
GC NA test 13 14 4
EO&T NA test 92 1 25
EO&T ASPAC test 21 1 14
Divested/JV ASPAC test 8 7 2
GC ASPAC test 7 1 4
GC NA test 13 1 4
ICG EMEA test 3 5 3
ICG NA test 4 5 3
ICG ASPAC test 129 5 60
EO&T NA test 92 20 25
EO&T ASPAC test 21 20 14
EO&T NA test 92 93 25
EO&T ASPAC test 21 93 14
ICG EMEA test 3 3 3
ICG NA test 4 3 3
ICG ASPAC test 129 3 60
GC ASPAC test 7 7 4
GC NA test 13 7 4
Here i'd like to get the results in the main query result same as which are getting while running separately

Report message to a moderator

Re: Appropriate join to the desired output [message #670821 is a reply to message #670820]

Fri, 27 July 2018 19:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Here i'd like to get the results in the main query result same as which are getting while running separately
I am not sure that I understand what above exactly means.

https://community.oracle.com/thread/4161653

SELECT a.project, 
       a.solution, 
       b.soa_sector, 
       b.soa_region, 
       Count(a.first_scan_id) cnt1 
FROM   table1 a 
       JOIN table3 b 
         ON b.project = a.project 
            AND b.scan_id = a.first_scan_id 
WHERE  a.project = 'test' 
GROUP  BY a.project, 
          a.solution, 
          b.soa_sector, 
          b.soa_region 
UNION 
SELECT a.project, 
       a.solution, 
       b.soa_sector, 
       b.soa_region, 
       Count(a.last_scan_id) cnt1 
FROM   table1 a 
       JOIN table3 b 
         ON b.project = a.project 
            AND b.scan_id = a.last_scan_id 
WHERE  a.project = 'test' 
GROUP  BY a.project, 
          a.solution, 
          b.soa_sector, 
          b.soa_region 
UNION 
SELECT a.project, 
       a.solution, 
       b.soa_sector, 
       b.soa_region, 
       Count(a.prior_scan_id) cnt1 
FROM   table1 a 
       JOIN table3 b 
         ON b.project = a.project 
            AND b.scan_id = a.prior_scan_id 
WHERE  a.project = 'test' 
GROUP  BY a.project, 
          a.solution, 
          b.soa_sector, 
          b.soa_region;

Report message to a moderator