1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| WITH client AS (SELECT 1 AS num, 'Agent notionnelle du crédit' AS nom FROM DUAL
UNION ALL SELECT 2, 'EDF' FROM DUAL
UNION ALL SELECT 3, 'ANC' FROM DUAL
UNION ALL SELECT 4, 'GDF' FROM DUAL
UNION ALL SELECT 5, 'A.N.C' FROM DUAL
UNION ALL SELECT 6, 'Agence notionnelle du commerce' FROM DUAL
UNION ALL SELECT 7, ' A N C ' FROM DUAL
)
SELECT num, nom,
TRANSLATE(nom, 'a' || TRANSLATE(LOWER(nom), 'abcdefghijklmnopqrstuvwxyz', 'a'), 'a') AS transl,
( SELECT MIN(c2.num)
FROM client c2
WHERE TRANSLATE(c2.nom, 'a' || TRANSLATE(LOWER(c2.nom), 'abcdefghijklmnopqrstuvwxyz', 'a'), 'a')
= TRANSLATE(c1.nom, 'a' || TRANSLATE(LOWER(c1.nom), 'abcdefghijklmnopqrstuvwxyz', 'a'), 'a')) AS meme_client
FROM CLIENT c1 |
Partager