Help to use IN keyword

[Anonymous]

can anyone please tell me, is there any keyword in archicad which is equivalent to sql's "IN" keyword????

i hav query like
select xx.parameters.type,xx.parameters.value,xx.parameters.name from Flat(objects,parameters) as xx where xx.parameters.variable_name='Element ID' and id='59E19E78-BE54-4D09-8CCC-91A35009DAE8' or xx.parameters.name='Family' and id='59E19E78-BE54-4D09-8CCC-91A35009DAE8'

and i want to use "IN" keyword near id in the above query, to include this one
select lb.object_id from objects as ob,labels as lb where ob.id=lb.object_id

[Karl Ottenstein]

Hi,

What you want to use is not clear - as you do not use "IN" in your example.

State the query exactly as you wish for it to be so that we know what it is that you want.

From the GS SQL Language Reference, the IN operator is allowed:

IN
Determines if a given value matches any value in a subquery or a list. If the value of <test expression>
is equal to any <expression> from the comma-separated list, the result value is TRUE. Otherwise, the
result value is FALSE. Using NOT IN negates the returned value.
Syntax
<test expression> [ NOT ] IN ( <expression> ... )
Arguments
test expression
Any valid Graphisoft SQL numeric expression.
expression...
A list of expressions to test for a match. All expressions must be of the same type as <test
expression>.
Result Types
BOOL

If you are on Windows and use the ODBC driver, then you can use MS Access and 'normal' SQL, including nested queries, inner joins or whatever, to get exactly what you need.

Karl

[Anonymous]

I hav query like this

select xx.parameters.type,xx.parameters.value,xx.parameters.name from Flat(objects,parameters) as xx where xx.parameters.variable_name='Element ID' and id='59E19E78-BE54-4D09-8CCC-91A35009DAE8' or xx.parameters.name='Family' and id='59E19E78-BE54-4D09-8CCC-91A35009DAE8'

and i want it to be like this, but this below query doesnt work, dont know y..

select xx.parameters.type,xx.parameters.value,xx.parameters.name from Flat(objects,parameters) as xx where xx.parameters.variable_name='Element ID' and id='59E19E78-BE54-4D09-8CCC-91A35009DAE8' or xx.parameters.name='Family' and (id IN select lb.object_id from objects as ob,labels as lb where ob.id=lb.object_id)

[Anonymous]

select xx.parameters.type,xx.parameters.value,xx.parameters.name from Flat(objects,parameters) as xx where xx.parameters.name='Family' and (id IN (select lb.object_id from objects as ob,labels as lb where ob.id=lb.object_id) )


when i execute the above query, it gives me the following error.....

Failed to parse SQL statement 'select xx.parameters.type,xx.parameters.value,xx.parameters.name from Flat(objects,parameters) as xx where xx.parameters.name='Family' and (id IN (select lb.object_id from objects as ob,labels as lb where ob.id=lb.object_id) )
' due to the following error(s):
Error: unexpected token: IN at [line: 1, column: 144]
Error: expecting RPAREN, found 'FROM' at [line: 1, column: 168]
Error: unexpected token: AS at [line: 1, column: 181]

[Anonymous]

This is my query,

select xx.parameters.type,xx.parameters.value,xx.parameters.name,id from Flat(objects,parameters) as xx where xx.parameters.variable_name IN ('eas_ID','eas_F','eas_D','eas_M','eas_U') and id='59E19E78-BE54-4D09-8CCC-91A35009DAE8'

When i execute the above query it shows me error.....

Is this is the correct way to use IN keyword ?

When i execute as below it works well, so guess its the problem with IN keyword...

select xx.parameters.type,xx.parameters.value,xx.parameters.name,id from Flat(objects,parameters) as xx where xx.parameters.variable_name ='eas_ID' and id='59E19E78-BE54-4D09-8CCC-91A35009DAE8'


Can any1 please figure out the problem?

[Karl Ottenstein]

The first use of IN is perhaps not allowed by the interpreter - as the 'in' expression is itself a query. The more recent use - a set of strings - should have worked I think. This very simple test worked for me:

select userid,height from walls where userid in ('Wall Ext-003','Wall Ext-004')

I'm afraid you may have to use trial and error, working from simple to complex. As mentioned, you may very well need to use an external database to access things via ODBC so that you can nest your queries and get exactly what you want.

Karl

PS Please do not start a new thread as you did that is basically identical to the first. I've merged the two.

[Anonymous]

Hi,

i started a new thread, since i messed up the earlier one with so many sql query statements. Hereafter i wont post different threads tat r identical .

similar to ur query, i also did a simple query using IN keyword, it is working for me too.. I cudnt figure out, y its not working for this query. And im doing trial n error, to get it work, yet no use.

you cud understand from my query, i was able to get the value if it is just one variable name, but not for more than one of it using IN keyword.....

actually, in my query im trying to get Parameter table's values which are inside object table. i cudnt see the [Parameter's variable name field] in the archicad help file, but i can see it in objects table, U plz try this query to understand wat im telling {select parameters from objects}.
In my query, in trying to use IN keyword for this parameters variable name, is it becoz i cudnt get my query work?

thanks
vidhya