ShMiL
09-07-2002, 11:38 AM
strSQL = "SHAPE (SHAPE {SELECT * FROM systems} " & _
"APPEND((SHAPE{SELECT * FROM categories} " & _
"APPEND({SELECT * FROM pix_data} " & _
"RELATE systems.sysID to categories.sysID) AS secLevel) " & _
"RELATE categories.catID to pix_date.catID) AS flevel) AS level"
this is the SQL code I use to put a three-leveled tree into a Recordset.
The table 'systems' is the top level, then 'categories' and the lowest branch is 'pix_data'.
In 'systems' I have the fiels 'sysID' which is the identifier for the systems.
In 'categories' I have 'sysID' which is related to the systems.sysID + I have the fiels 'catID' which identifies the category.
In pix_data I have the fiels 'catID' which related to the categories.catID + I have the field 'id' which identifies the records (in pix_data).
I'm trying to shape those queries into one recordset but it doesn't seems to work, the error I get is:
Data shape command text contains a syntax error at or near position 124 in the command. The text near the error is: " RELATE systems.sysID to categories.sysID)".
Please help me to find what's wrong.
Thanks alot!
P.S
In my experience, DATASHAPE DOES work on one table - but using multiple tables seems to be problematic.
"APPEND((SHAPE{SELECT * FROM categories} " & _
"APPEND({SELECT * FROM pix_data} " & _
"RELATE systems.sysID to categories.sysID) AS secLevel) " & _
"RELATE categories.catID to pix_date.catID) AS flevel) AS level"
this is the SQL code I use to put a three-leveled tree into a Recordset.
The table 'systems' is the top level, then 'categories' and the lowest branch is 'pix_data'.
In 'systems' I have the fiels 'sysID' which is the identifier for the systems.
In 'categories' I have 'sysID' which is related to the systems.sysID + I have the fiels 'catID' which identifies the category.
In pix_data I have the fiels 'catID' which related to the categories.catID + I have the field 'id' which identifies the records (in pix_data).
I'm trying to shape those queries into one recordset but it doesn't seems to work, the error I get is:
Data shape command text contains a syntax error at or near position 124 in the command. The text near the error is: " RELATE systems.sysID to categories.sysID)".
Please help me to find what's wrong.
Thanks alot!
P.S
In my experience, DATASHAPE DOES work on one table - but using multiple tables seems to be problematic.