Need a single SQL query 1

[rach18]

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 combinations1,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 combination95,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 combination95,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 combination95,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

 

[LKBrwnDBA]

How about this possibility:

SELECT 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

 

[LKBrwnDBA]

Or maybe this one:

WITH 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]

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]

Assuming you don't, the analytic solution would be something like:

create 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