Using a query result in another query

570
3
Jump to solution
04-22-2020 09:46 PM
KerryKang
Occasional Contributor II

Hello all,

I am a GIS analyst and very new to Python API. I hope to get some advice on creating syntax.

Here is my scenario. 

1. get the record count in feature layer. this will be used to grab the record with max object id. (the very last object id will be matched with row count always).

query_result1 = workingLayer.query(return_count_only=True)

query_result1

49

2. Using the result from #1 in another query. 'where' part is where I am having an issue with. I am not sure how I can use the result from #1 with the where syntax.

query_result2 = workingLayer.query(where = ,  out_fields='Region, Date')

I've tried with what I used to with arcpy...something like this. But not working. 

whereClause = "'ObjectId=" + str(query_result1) + "'"

query_result2 = workingLayer.query(where = whereClause,  out_fields='Region, Date')

Cheers,

K

0 Kudos
1 Solution

Accepted Solutions
WillHouston
New Contributor III

From:

whereClause = "'ObjectId=" + str(query_result1) + "'"

This looks to me like your resulting where clause would be

'ObjectId=49'

Try it without the single quotes:

whereClause = "ObjectId=" + str(query_result1)

In general I prefer the string format so I can better see the final string, but the result should be the same:

whereClause = "ObjectId = {0}".format(query_result1)

Also depending on your database management system, the case of "ObjectId" ("OBJECTID") might matter.

Edit in case anyone else sees this and wonders why I suggested removing the single quotes: I made an assumption that the query function is building a database query by inserting that parameter. A basic query with a WHERE clause might be, "SELECT * FROM working_table WHERE ObjectId = 1". By putting that parameter in single quotes, the query language treats it as a string: "... WHERE 'ObjectId=49'", so it is looking to compare that to a text field in the database. The query language would expect "WHERE 'ObjectId=49' = some_text_field".

Different query languages might return all results, return no results, or return an error. That I'm not sure about.

View solution in original post

3 Replies
WillHouston
New Contributor III

From:

whereClause = "'ObjectId=" + str(query_result1) + "'"

This looks to me like your resulting where clause would be

'ObjectId=49'

Try it without the single quotes:

whereClause = "ObjectId=" + str(query_result1)

In general I prefer the string format so I can better see the final string, but the result should be the same:

whereClause = "ObjectId = {0}".format(query_result1)

Also depending on your database management system, the case of "ObjectId" ("OBJECTID") might matter.

Edit in case anyone else sees this and wonders why I suggested removing the single quotes: I made an assumption that the query function is building a database query by inserting that parameter. A basic query with a WHERE clause might be, "SELECT * FROM working_table WHERE ObjectId = 1". By putting that parameter in single quotes, the query language treats it as a string: "... WHERE 'ObjectId=49'", so it is looking to compare that to a text field in the database. The query language would expect "WHERE 'ObjectId=49' = some_text_field".

Different query languages might return all results, return no results, or return an error. That I'm not sure about.

KerryKang
Occasional Contributor II

It worked, thanks Will!

K.

JoshuaBixby
MVP Esteemed Contributor

If someone's response answers your question, please mark it correct to close out the question.

0 Kudos