Using input parameters in where clause properly

[EikeMüller]

Greetings to GIS community,

I am fairly new with this and I've got a (probably simple) problem with using the right syntax in a where_clause and hope somebody can help me. The script should compare 2 tables after selecting specific attributes. I want the User to fill in the parameters he wants to compare (by GetParameterAsText)
Here is a short extract of the script:

LAND_CD = arcpy.GetParameterAsText(0)
WA_CD = arcpy.GetParameterAsText(1)
WB_CAT = arcpy.GetParameterAsText(2)
DE_MEAS_CD = arcpy.GetParameterAsText(3)

test_msrprog_2009 = r"Y:\GIS\\Temp_GIS\\Scratch_GeoDB.gdb\\test_msrprog_2009"
test_msrprog_2012 = r"Y:\GIS\\Temp_GIS\\Scratch_GeoDB.gdb\\test_msrprog_2012"
query_test_msrprog_2009 = r"Y:\GIS\\Temp_GIS\\Scratch_GeoDB.gdb\\query_test_msrprog_2009"

newLAND_CD = arcpy.AddFieldDelimiters(test_msrprog_2009, LAND_CD)
newWA_CD = arcpy.AddFieldDelimiters(test_msrprog_2009, WA_CD)
newWB_CAT = arcpy.AddFieldDelimiters(test_msrprog_2009, WB_CAT)
newDE_MEAS_CD = arcpy.AddFieldDelimiters(test_msrprog_2009, DE_MEAS_CD)

where = "\"LAND_CD\" = newLAND_CD AND \"WA_CD\" = newWA_CD  AND \"WB_CAT\" = newWB_CAT AND \"DE_MEAS_CD\" = newDE_MEAS_CD"

# Process: Table Select (3)
arcpy.TableSelect_analysis(test_msrprog_2009, query_test_msrprog_2009, where)

LAND_CD and WB_CAT are String.
WA_CD and DE_MEAS_CD are Double.

The whole Script works, if i fill my values in the code manually. But not with "GetParameterAsText", then i get ERROR 000714.
I think failure must be inside that where_clause.

[T__WayneWhitley]

I'll comment briefly, because you may have multiple errors in the code - why it is good to trap errors or include a note about where your code crashes.  I suggest this, print your 'where' query, make sure your strings print correctly (enclosed in quotes), etc.  Your entire query appears to be reading as a single string, rather than making the variable substitutions in the string query construction.
Also, look at your SQL syntax...

You may have to form the query in parts, and there are many good examples online -- for example, something like (for a string val):

>>> newLAND_CD = 'someText'
>>> where = "\"LAND_CD" + "\" = " + newLAND_CD
>>> print where
"LAND_CD" = someText
>>> 

[MathewCoyle]

To add to what Wayne said; you seem to be confusing the use of the Add Field Delimiters tool. This is to add delimiters to the field, not the attributes of the field. What specifically are the parameters you are passing to the script?

Here's the help page on using the Add Field Delimiters tool.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//000v0000004n000000

[T__WayneWhitley]

Thanks Mathew, I wondered about that too and need to check that out too!  Haven't used that functionality yet.  Thanks for the info.

So probably more like this (whatever text you want compared, and test this to be sure):

where = newLAND_CD + " = " + newWB_CAT
...and so on...but for some reason this still doesn't look right - again, check your SQL

[EikeMüller]

First of all, thanks for your advices.
The SQL-Expression was exported from the ModelBuilder before. It works if i use real values and no variables (so the expression cannot be wrong).

where = "\"LAND_CD\" = newLAND_CD AND \"WA_CD\" = newWA_CD AND \"WB_CAT\" = newWB_CAT AND \"DE_MEAS_CD\" = newDE_MEAS_CD"


As you said, the script assumes the whole Expression to be one string.
But anyways, we solved the problem completely without python and by using Access.
Nevertheless its interesting in general, how to integrate input parameters into an expression...?! (both strings and doubles)

[dariomasante]

Hi all, I know it is an old thread, but might be useful anyway. After spending a whole afternoon checking my script I found out there was a very simple answer, error 000714 was not a script error, but a file extension issue.
After finishing your script, be sure to save it with .py extension, then your tool in arcgis will work fine.
Hope it helps!