Thnk You Tonkuma very much, your SQL AND UDF are working perfect
Leny G.
Tonkuma wrote:
>Is this satisfied your requirements?
>
>CREATE FUNCTION three_digit_numbers(in_s VARCHAR(50) )
>RETURNS VARCHAR(100)
>LANGUAGE SQL
>READS SQL DATA
>DETERMINISTIC
>NO EXTERNAL ACTION
>RETURN
>WITH add_leading_zero
>(ordinal, index, result) AS (
>VALUES (0, 0, CAST(in_s AS VARCHAR(100)) )
>/* */ UNION ALL /* */
>SELECT pre.ordinal + 1
> , new.index
> , INSERT( pre.result
> , pre.ordinal*4 + 1
> , 0
> , SUBSTR('00', 1, 4 - new.index + pre.index) )
> FROM add_leading_zero pre
> , (SELECT index
> , INT(ROWNUMBER() OVER(ORDER BY index) ) ordinal
> FROM (SELECT 1+n1+10*n2
> FROM (VALUES 0,1,2,3,4,5,6,7,8,9) N(n1)
> , (VALUES 0,1,2,3,4,5,6,7,8,9) N(n2)
> WHERE n1+10*n2 <= LENGTH(in_s)
> ) N(index)
> WHERE SUBSTR(in_s||'.',index,1) = '.'
> ) new
> WHERE pre.ordinal < 1000
> AND new.ordinal = pre.ordinal + 1
>)
>SELECT result
> FROM add_leading_zero
> WHERE ordinal
> = (SELECT MAX(ordinal)
> FROM add_leading_zero
> )
>;
>------------------------------------------------------------------------------
>DB20000I The SQL command completed successfully.
>
>------------------------------ Commands Entered
>------------------------------
>WITH
>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')
>)
>SELECT c1
> FROM T3
> WHERE SUBSTR(c1,1,1) IN ('1', '2')
> ORDER BY
> three_digit_numbers(c1);
>------------------------------------------------------------------------------
>
>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
>
> 14 record(s) selected.

--
Message posted via http://www.dbmonster.com