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