Here's a chopped down query that demonstrates my difficulty do sql_s = "select * from tbl_tv where id in " & _ "(" & _ "select id from tbl_tv where " & _ "new_tnode = '' and " & _ "pnode = 0 or " & _ "pnode in (select tnode from tbl_tv where new_tnode <> '')" & _ ")limit 1;" exit loop when you've got no more records with an empty new_tnode loop
I'm trying to draw a treeview using a stored table i.e. CREATE TABLE tbl_tv( id integer primary key, pnode text, anode text, tnode text, new_tnode text, txt text, cnodes text) pnode is parent node anode is after node...ie node that comes before the one your going to draw tnode is old this node new_tnode is handle of treeview node you've JUST drawn txt is label of treeview node cnodes are old handles of child nodes All the old node handles are useful because they show the tree structure If I limit the query to sql_s = "select * from tbl_tv where id in " & _ "(" & _ "select id from tbl_tv where " & _ "new_tnode = '' " & _ ) limit 1;" and write a 1 in each returned rec's new_tnode then I can cycle through all nodes fine. Unfortunately the more complete query at the top seems to keep returning the first non-0 parent, non-0 after node record even though it's got a 1 in the new_tnode field which I THOUGHT I'd precluded i.e. only want to return records which have a '' newtnode but which have pnode and anode values that match tnodes in records which have a new_tnode of 1. Hope I explained my self Heres the full procedure just in case it helps SUB aaTbl_to_tv(frm$,tv_id&) DEF_SQL_VARS LOCAL i&, hTv&, ub& LOCAL form$, props$ LOCAL t AS tTv_rec form$ = gTv.form props$ = gTv.props sqlite3_open("db_spread",sql_h???) DO sql_s = "select * from tbl_tv where id in " & _ "(" & _ "select id from tbl_tv where " & _ "new_tnode = '' and " & _ "pnode = 0 or " & _ "pnode in (select tnode from tbl_tv where new_tnode <> '')" & _ ")limit 1;" ' "and " & _ ' "pnode = 0 " & _ ' "or " & _ ' "pnode in " & _ ' "(select tnode from tbl_tv where new_tnode <> '') " & _ ' "and " & _ ' "anode = 0 " & _ ' "or " & _ ' "anode in " & _ ' "(select tnode from tbl_tv where new_tnode <> '') " & _ ' ") limit 1;" do_it IF UBOUND(sql_a) <> -1 THEN aaSql_to_tTv_rec(sql_a(),t) sql_s = "UPDATE tbl_tv SET new_tnode=fld_val WHERE id=id_val;" REPLACE "fld_val" WITH $SQ & "1" & $SQ IN sql_s REPLACE "id_val" WITH t.id IN sql_s DO_IT 'aaTest_tTv_rec(t) 'now find new parent and after nodes IF t.pnode <> "0" THEN sql_s = "select new_tnode from tbl_tv " & _ "where tnode = tnode_val;" REPLACE "tnode_val" WITH t.pnode IN sql_s do_it IF UBOUND(sql_a) THEN ? t.lbl & " setting parent to " & sql_a(1) t.pnode = sql_a(1) ELSE ? t.lbl & " parent is 0" END IF ELSE END IF IF t.anode <> "0" THEN sql_s = "select new_tnode from tbl_tv " & _ "where tnode = tnode_val;" REPLACE "tnode_val" WITH t.anode IN sql_s do_it IF UBOUND(sql_a) THEN t.anode = sql_a(1) ? t.lbl & " setting t.anode to " & sql_a(1) ELSE ? t.lbl & "no rec returned for anode" END IF ELSE ? t.lbl & "changing t.anode from 0 to " & t.pnode t.anode = t.pnode END IF hTV& = EZ_AddTVItem( _ form$, gTv.tv_id, _ VAL(t.pnode), VAL(t.pnode), BYCOPY t.lbl, _ 0,0,"" _ ) ELSE ? "no more blank new_tnode recs",,FUNCNAME$ EXIT DO END IF LOOP sqlite3_close(sql_h???) END SUB _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users