Concatenate string and variables in where clause?

[ducksunlimited]

Dear All,

Can anybody correct the syntax error in the following codes? I use personal GDB. I knew the probelm is with the where clause, but don't know how to fix it. thanks,

...
...
uid = row.GetValue('UID')
state = row.GetValue('STATE')

gp.Select_analysis(USA_State, USA_State_Select, "UID <> " + str(uid) AND "STATE = " + state)

...
...

[ChrisMathers]

You need to build the string outside of the tool perameters so that you can wrap parts of it in quotes for the tool. Remember this isnt just a string, its and SQL statement so you have to comply with SQL conventions.

>>>where="UID <> \'" +str(uid) + "\' AND STATE = \'" +state +"\'"
"UID <> 'oid' AND STATE = 'state'"

gp.Select_analysis(USA_State, USA_State_Select, where)

[ducksunlimited]

thanks for the reply

because i use mdb, i changed your code to where="[UID] <> \'" +str(uid) + "\' AND [STATE] = \'" +state +"\'"

and returned the error:

An invalid SQL statement was used. [SELECT * FROM USA_STATE WHERE [UID] <> '1' AND [STATE] = 'IL']

to me, this is a correct SQL expression. but it appreared as an error.

any help?

thanks

You need to build the string outside of the tool perameters so that you can wrap parts of it in quotes for the tool. Remember this isnt just a string, its and SQL statement so you have to comply with SQL conventions.

>>>where="UID <> \'" +str(uid) + "\' AND STATE = \'" +state +"\'"
"UID <> 'oid' AND STATE = 'state'"

gp.Select_analysis(USA_State, USA_State_Select, where)

[RDHarles]

Try this one (singles AND doubles around variables, brackets around PGDB's):

qry = "[UID] <> '"+uid+"' and [STATE] = '"+state+"'"
gp.Select_analysis(USA_State, USA_State_Select, qry)

[BruceHarold]

Hi

Don't forget that you can make your code workspace-portable by using the AddFieldDelimiters method to wrap field names, and Python's repr function to quote string literals in the SQL expression.

Something like:

uid =  row.getValue('uid')
state = row.getValue('state')
ws = gp.Describe(USA_State).path
gp.Select_analysis(USA_State, USA_State_Select, gp.AddFieldDelimiters(ws,'UID') + " <> " + repr(uid) AND " + gp.AddFieldDelimiters(ws,'STATE')  + " = " + repr(state))

Regards