Thank' s everybody for help.
The reason why i need this conversion is to sort the following rows:
Here is my solution using nested tree model:
12. GENERIC SOLUTION USING NESTED TREE APROACH TO SORT UNSOrTABLE
1(1,22)
2 (23,28)
1.1 1.2 1.2.1.2 1.3.2.7 1.3.3.7.4 1.4.1 1.4.4 1.10.1 1.10.1.2.2
1.
22.99.1 2.7.7 2.8.11
(2,3)(4,5) (6,7) (8,9) (10,11) (12,13) (14,15) (16,17) (18,19)
(20,21) (24,25) (26,27)
WITH T1 (L_NUM,R_NUM) AS
(VALUES(1,22),
(2,3),
(6,7),
(16,17),
(18,19),
(20,21),
(4,5),
(23,28),
(24,25),
(26,27),
(14,15),
(10,11),
(8,9),
(12,13)),
T2(RN,L_NUM,R_NUM) AS
(SELECT ROW_NUMBER() OVER(),L_NUM,R_NUM FROM T1),
T3 (C1) AS
(VALUES ('1'),
('1.1'),
('1.2.1.2'),
('1.10.1'),
('1.10.1.2.2'),
('1.22.99.1'),
('1.2'),
('2'),
('2.7.7'),
('2.8.11'),
('1.4.4'),
('1.3.3.7.4'),
('1.3.2.7'),
('1.4.1')),
T4(RN,C1) AS
(SELECT ROW_NUMBER() OVER(),C1 FROM T3),
T5(L_NUM,R_NUM,C1) AS
(SELECT L_NUM,R_NUM,C1 FROM T2,T4
WHERE T2.RN = T4.RN)
SELECT F1.C1
FROM T5 AS F1, T5 AS F2
WHERE F1.L_NUM BETWEEN F2.L_NUM AND F2.R_NUM
AND F2.C1 IN('1','2')
ORDER BY F1.L_NUM;
C1
----------
1
1.1
1.2
1.2.1.2
1.3.2.7
1.3.3.7.4
1.4.1
1.4.4
1.10.1
1.10.1.2.2
1.22.99.1
2
2.7.7
2.8.11
Serman D. wrote:
>> Any idea how to produce requested result?
>
>I would have used perl:
>
>$ cat /tmp/foo.pl
>use strict;
>use warnings;
>
>while(my $line = <DATA>){
> print join(q{.}, map { sprintf("%03d", $_); } split(/\./,
>$line));
>}
>
>__DATA__
>1.4.1
>1.10.1
>1.10.1.2.2
>1.22.99.1
>2
>2.8.11
>2.7.7
>
>$ perl -wl /tmp/foo.pl
>001.004.001
>001.010.001
>001.010.001.002.002
>001.022.099.001
>002
>002.008.011
>002.007.007
>
>--
>Serman D.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200805/1
|
