Concatenate string and variables in where clause?

Question

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)

...
...

bharold · Answer

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

RDHarles · Answer

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)

ducksunlimited · Answer

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) MikeMinami_0-1639696434846.png