Rinovo wrote:
Hi!
I'm using Archicad SQL queries and i would like to select few parameters from all of them.
Single parameter was no problem to get, but got problems getting more.
Tried using this structure : SELECT *
FROM
flat(objects,parameters) as T_1,
flat(objects,parameters) as T_2
WHERE
(T_1.library_part_name = 'Built-in Elevation Marker')
and
(T_1.parameters.variable_name = 'AC_TextPen_1')
and
(T_2.parameters.variable_name = 'Tst_1')
and
(T_1.parameters.value = T_2.parameters.value)
from : http://archicad-talk.graphisoft.com/viewtopic.php?t=26969
Archicad just freezes, no error nothing just not responding.
Any suggestions ?
Yes, I have this suggestion: The error message that sometimes appears gives a clue. It says something like the column isn't in the table (T_1 in your cases). Don't use the * in the SELECT statement. Rather, make sure that the SELECT statement names or specifies the columns containing values you are querying in the WHERE clause(s). For example, it may be
"SELECT T_1.parameters.variable_name, T_2.parameters.variable_name" and so on. Then the WHERE clause will actually query and name a column that appears in the table you're creating with FLAT (temporarily in the background, I guess). Also, I like to add "OBJECTS.ID, OBJECTS.USERID, OBJECTS.LIBRARY_PART_NAME" in the SELECT statement immediately following the word SELECT. It helps me get and keep my "data bearings."
Generally speaking because the SQL documentation for AC is so sketchy at the user level, and the SQL92 version is only partly supported, -- how about a COUNT statement? -- experiment with SQL queries systematically, starting with extremely simple statements and gradually building them up to more complex ones. A thorough understanding of FLAT and what it does, is important.
All that having been said, using JOINs is typically preferred over many ANDed WHERE clauses. They're often faster as the query complexity increases.
Also, I found it indispensable to use something like Wordpad to catalog the SQL statements I've created that WORK, and identify the ones that FAIL, what they do (or don't), and then paste them into the Query window. It may help to keep Task Manager open to check on memory usage for those queries that run wild. I've had some run for a couple of hours and (inefficiently) succeed, and many others just MIiA. Best of luck. Check out OBDC.
