|
|
rach18 (Programmer) |
6 Feb 09 5:23 |
Hi,
Need a "single" SQL query to retrieve records. The SQL query can be a nested/correlated one.
Example:
Table name: table1
Keyfields: (scr,cdi,rax)
SELECT scr,cdi,rax,srow,srti,schg
FROM table1
WHERE rc_struct_flag='S' and tron=-1 and hostclli='MTRLPQQQ00T'
scr cdi rax srow srti schg
1 1 DFLT 1 REGL 6
1 1 DFLT 2 819 74
1 1 DFLT 3 REGL 6
1 2 DFLT 1 809 74
1 2 DFLT 2 819 74
1 2 DFLT 3 REGL 110
1 3 DFLT 1 REGL 1
1 3 DFLT 2 819 74
1 3 DFLT 3 819 74
1 5 DFLT 1 809 74
1 5 DFLT 2 819 74
1 5 DFLT 3 REGL 110
1 11 DFLT 1 809 74
1 11 DFLT 2 REGL 6
1 11 DFLT 3 819 74
1 19 DFLT 1 809 74
1 19 DFLT 2 809 74
1 19 DFLT 3 809 74
1 20 DFLT 1 REGL 6
1 20 DFLT 2 REGL 6
1 20 DFLT 3 819 74
1 21 DFLT 1 REGL 6
1 21 DFLT 2 REGL 6
1 21 DFLT 3 819 74
1 22 DFLT 1 REGL
1 22 DFLT 2 REGL 6
1 22 DFLT 3 101 6
1 200 DFLT 1 REGL 6
1 200 DFLT 2 REGL 6
1 200 DFLT 3 REGL 6
1 211 DFLT 1 REGL 1
1 211 DFLT 2 819 74
1 211 DFLT 3 819 74
95 1 DFLT 1 REGL 6
95 1 DFLT 2 2 6
95 1 DFLT 3 107 6
95 2 DFLT 1 801 6
95 2 DFLT 2 819 74
95 2 DFLT 3 REGL 6
95 3 DFLT 1 REGL 1
95 3 DFLT 2 808 74
95 3 DFLT 3 819 74
95 5 DFLT 1 801
95 5 DFLT 2 819
95 5 DFLT 3 REGL
95 19 DFLT 1 809 74
95 19 DFLT 2 809 6
95 19 DFLT 3 809 74
95 20 DFLT 1 280 6
95 20 DFLT 2 280 6
95 20 DFLT 3 2108 6
95 21 DFLT 1 REGL 6
95 21 DFLT 2 REGL 6
95 21 DFLT 3 808 74
95 22 DFLT 1 REGL 6
95 22 DFLT 2 819 74
95 22 DFLT 3 101 6
....
Need a single query which could fetch us the following when queried for the criteria: rc_struct_flag='S' and tron=-1 and hostclli='MTRLPQQQ00T' and schg='74'.
The records should be retrieved in such a way that if any one row among srow=1 or srow=2 or srow=3 has schg='74', all the srows i.e., having same keyfield (scr,cdi,rax) combination should be fetched. Number of srows need not be 3 always. It differs from each table in the database.
scr cdi rax srow srti schg
1 1 DFLT 1 REGL 6
1 1 DFLT 2 819 74
1 1 DFLT 3 REGL 6
1 2 DFLT 1 809 74
1 2 DFLT 2 819 74
1 2 DFLT 3 REGL 110
1 3 DFLT 1 REGL 1
1 3 DFLT 2 819 74
1 3 DFLT 3 819 74
1 5 DFLT 1 809 74
1 5 DFLT 2 819 74
1 5 DFLT 3 REGL 110
1 11 DFLT 1 809 74
1 11 DFLT 2 REGL 6
1 11 DFLT 3 819 74
1 19 DFLT 1 809 74
1 19 DFLT 2 809 74
1 19 DFLT 3 809 74
1 20 DFLT 1 REGL 6
1 20 DFLT 2 REGL 6
1 20 DFLT 3 819 74
1 21 DFLT 1 REGL 6
1 21 DFLT 2 REGL 6
1 21 DFLT 3 819 74 [Records with keyfield combinations:(1,22,DFLT),(1,200,DFLT) are not fetched since schg='74' is
not present in neither of the srows]
1 211 DFLT 1 REGL 1
1 211 DFLT 2 819 74
1 211 DFLT 3 819 74
95 2 DFLT 1 801 6 [Records with keyfield combination:(95,1,DFLT) are not fetched]
95 2 DFLT 2 819 74
95 2 DFLT 3 REGL 6
95 3 DFLT 1 REGL 1
95 3 DFLT 2 808 74
95 3 DFLT 3 819 74 [Records with keyfield combination:(95,5,DFLT) are not fetched]
95 19 DFLT 1 809 74
95 19 DFLT 2 809 6
95 19 DFLT 3 809 74 [Records with keyfield combination:(95,20,DFLT) are not fetched]
95 21 DFLT 1 REGL 6
95 21 DFLT 2 REGL 6
95 21 DFLT 3 808 74
95 22 DFLT 1 REGL 6
95 22 DFLT 2 819 74
95 22 DFLT 3 101 6
....
The query should be somewhat similar to the below. Cannot use the following, since multiple attributes are not supported while using comparison operator IN.
SELECT scr,cdi,rax,srow,srti,schg
FROM table1
WHERE (scr,cdi,rax) IN (SELECT scr,cdi,rax FROM table1 WHERE where rc_struct_flag='S' and tron=-1 and hostclli='MTRLPQQQ00T' and schg='74' );
Any help would be appreciated.
Thanks,
Rach
|
|
How about this possibility: CODESELECT scr, cdi, rax, srow, srti, schg
FROM table1
WHERE scr||','||cdi||','||rax IN (
SELECT scr||',||cdi||',||rax
FROM table1
WHERE rc_struct_flag = 'S'
AND tron = -1
AND hostclli = 'MTRLPQQQ00T'
AND schg = '74');  ----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
|
|
Or maybe this one: CODEWITH qry1 AS
(SELECT *
FROM table1
WHERE rc_struct_flag = 'S' AND tron = -1
AND hostclli = 'MTRLPQQQ00T')
SELECT scr, cdi, rax, srow, srti, schg
FROM qry1 a
WHERE EXISTS (
SELECT '?'
FROM qry1 b
WHERE b.scr = a.scr
AND b.cdi = a.cdi
AND b.rax = a.rax
AND schg = '74');  ----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
|
|
|
Dagon (MIS) |
6 Feb 09 8:21 |
Is it possible to have records with srow not in (1,2,3) that you would be interested in returning ? E.g. could there be a record with srow=4 or some other value ? |
|
|
Dagon (MIS) |
6 Feb 09 8:37 |
Assuming you don't, the analytic solution would be something like: CODEcreate table testrows (scr number, cdi number, rax varchar2(20), srow number, srti varchar2(20), schg number)
insert into testrows
values (1, 22, 'DFLT', 1, 'REGL', null);
insert into testrows
values
(1, 22, 'DFLT', 2, 'REGL',6);
insert into testrows
values
(1, 22, 'DFLT', 3, '101', 6);
insert into testrows
values
(1, 21, 'DFLT', 1, 'REGL',6);
insert into testrows
values
(1, 21, 'DFLT', 2, 'REGL',6);
insert into testrows
values
(1, 21, 'DFLT', 3, '819',74);
select * from testrows
select scr, cdi, rax, srow, srti, schg
from
(select scr, cdi, rax, srow, srti, schg, count(case when schg = 74 then 1 end) over (partition by scr, cdi, rax) as cnt74
from testrows)
where cnt74 > 0
1 21 DFLT 2 REGL 6
1 21 DFLT 1 REGL 6
1 21 DFLT 3 819 74
|
|
|
 |
Talk To Other Members
Read More Threads Like This One