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
48
49
50
51
52
53
| SQL> with rn_ocrd as (
2 select cardcode, cardname,
3 row_number() over(order by cardcode desc) as rn
4 from OCRD o
5 where cardcode like 'F0%'
6 ),
7 rec_rn_ocrd (cardcode, cardname, comp_cardcode, comp_cardname, trou, rn) as (
8 select cardcode, cardname, cardcode, cardname, 0, 1
9 from rn_ocrd
10 where rn = 1
11 union all
12 select r.cardcode, r.cardname,
13 case when r.cardname < rec.comp_cardname
14 then r.cardcode
15 else rec.comp_cardcode
16 end as comp_cardcode,
17 case when r.cardname < rec.comp_cardname
18 then r.cardname
19 else rec.comp_cardname
20 end as comp_cardname,
21 case when cast(substr(r.cardcode, 2, 5) as int) <> cast(substr(rec.cardcode, 2, 5) as int)- 1
22 then 1
23 else 0
24 end as trou,
25 rec.rn + 1
26 from rn_ocrd r
27 join rec_rn_ocrd rec on r.rn = rec.rn + 1
28 ),
29 tri_orcd as (
30 select distinct comp_cardcode as cardcode, comp_cardname as cardname, trou
31 from rec_rn_ocrd
32 ),
33 rn_tri_orcd as (
34 select cardcode, cardname, trou, row_number() over(order by cardcode desc) as rn
35 from tri_orcd
36 ),
37 dispo_cardcode as (
38 select cardcode, cardname
39 from rn_tri_orcd
40 where rn = 1
41 or trou = 1
42 )
43 select 'F'||lpad(coalesce(max(cast(substr(case when cardname < 'ASC2'
44 then cardcode
45 end, 2, 5) as int)) + 1,
46 max(cast(substr(cardcode, 2, 5) as int)) + 1), 5, '0') as res
47 from dispo_cardcode;
RES
---------------------
F00019
SQL> |
Aide pour ré-indexation alphabétique
Répondre avec citation
Envoyé par oliviers92
Partager