I think this should work, but with current almagation avalaible for
download sqlite-amalgamation-201401171527, I get nothing.
Has anyone a clue ?
drop table if exists gen9;
drop table if exists genx;
drop table if exists initial;
create table gen9(z);
insert into gen9 values
('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
create table initial (s, ind) ;
insert into initial select sud, instr( sud, ' ') from
(SELECT '53 7 6 195 98 6 8 6 34 8 3 17 2 6 6 28
419 5 8 79' as sud) as q ;
WITH RECURSIVE x( s, ind ) AS
(SELECT * from initial
UNION ALL
SELECT substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 ) as s
,
instr(substr('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',1,
ind)
|| substr( s, ind + 1 ), ' ' ) as ind
FROM x , gen9 as z
WHERE ind > 0
AND NOT EXISTS ( SELECT NULL
FROM gen9 as lp
WHERE z.z = substr( s, ( (ind - 1 ) / 9 ) * 9 +lp.z, 1 )
OR
z.z = substr( s, (ind -(9*( ind - 1)/ 9 )) - 8 + lp.z *
9, 1 )
OR
z.z = substr( s, (( ind - 1 ) / 3 - ( ( ind - 1 ) / 3 )/
3 ) * 3
+ ( ( ind - 1 ) / 27 ) * 27 + lp.z
+ ( ( lp.z - 1 ) / 3 ) * 6
, 1 )
))
SELECT s FROM x
--even without the test below, I can't read back the initial record of the
recursive CTE
--WHERE ind = 0;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
