1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL>
SQL> With Table1 As (
2 Select cast('001' As varchar2(5)) As Id, 1 As version, cast('123' As varchar2(10)) As codes from dual union all
3 Select '001' As Id, 2 As version, '123' As codes from dual union all
4 Select '001' As Id, 2 As version, '456' As codes from dual union all
5 Select '001' As Id, 3 As version, '123' As codes from dual union all
6 Select '001' As Id, 3 As version, '222' As codes from dual union all
7 Select '001' As Id, 3 As version, '789' As codes from dual union all
8 Select '001' As Id, 4 As version, '135' As codes from dual union all
9 Select '001' As Id, 4 As version, '222' As codes from dual union all
10 Select '001' As Id, 4 As version, '246' As codes from dual union all
11 Select '002' As Id, 1 As version, '111' As codes from dual union all
12 Select '002' As Id, 1 As version, '222' As codes from dual union all
13 Select '002' As Id, 2 As version, '222' As codes from dual union all
14 Select '002' As Id, 2 As version, '789' As codes from dual union all
15 Select '003' As Id, 1 As version, '222' As codes from dual union all
16 Select '003' As Id, 1 As version, '444' As codes from dual union all
17 Select '003' As Id, 1 As version, '789' As codes from dual
18 ),
19 TABLE2 As (
20 Select cast('001' As varchar2(5)) As Id, 4 As version from dual union all
21 Select '002' As Id, 2 As version from dual union all
22 Select '003' As Id, 1 As version from dual
23 ), vals As (
24 Select cast('222' As varchar2(10)) As codes from dual union all
25 Select '789' As codes from dual
26 )
27 Select t1.id
28 from TABLE1 t1
29 Join
30 TABLE2 t2
31 On t1.id = t2.id
32 And t1.version = t2.version
33 Group BY t1.id
34 HAVING (SELECT Cast(Collect(codes) AS varchar2_tt) FROM vals)
35 Submultiset of Cast(collect(codes) AS varchar2_tt)
36 /
ID
-----
002
003
SQL> |
Partager