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
|
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0
Connected as mni
SQL>
SQL> WITH t AS (
2 SELECT 'A,B,C' lettre FROM dual
3 ), list As
4 (
5 SELECT extractvalue(COLUMN_VALUE,'/x') AS lettre
6 FROM t,
7 TABLE( xmlsequence(EXTRACT(XMLTYPE('<list><x>'|| REPLACE(lettre,',','</x><x>') ||'</x></list>')
8 , '/list/x')))
9 )
10 , Data As
11 (
12 SELECT 'A' lettre FROM dual union ALL
13 SELECT 'B' lettre FROM dual union ALL
14 SELECT 'D' lettre FROM dual union ALL
15 SELECT 'E' lettre FROM dual union ALL
16 SELECT 'F' lettre FROM dual
17 )
18 SELECT list.lettre, DATA.lettre
19 FROM list
20 LEFT OUTER JOIN
21 DATA ON(DATA.lettre = list.lettre)
22 WHERE DATA.lettre IS NULL
23 /
LETTRE LETTRE
-------------------------------------------------------------------------------- ------
C |
Partager