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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users