Home » RDBMS Server » Performance Tuning » Appropriate join to the desired output (Oracle 11.2)
| 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
|
|
|
|
Current Time: Wed May 08 07:17:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
