topic SQL Select Query in Geoprocessing Questions

SQL Select Query

JontyKnox — Tue, 29 Nov 2011 19:17:11 GMT

Hi there,

I am trying to construct an SQL query using the 'Select' tool. Essentially what I am trying to figure out is how to select the first row in an attribute table, with an additional statement only allowing this record to be selected if my Boolean field (in the same attribute table) is equal to 0.

For a bit of background, this step fits into a wider process that I am running in Model builder. The tool factors into a structure that would then create a buffer around the highest ranked points (FID moving from 0 down) in the attribute table. It would then exclude those points that fall within the buffered regions around the highest ranked points (by assigning them a value of 1). This process would repeat until all points had a Boolean field value of 1.

If you guys could lend me a hand that would be great,

Cheers, Jonty

Re: SQL Select Query

BruceHarold — Wed, 30 Nov 2011 13:54:42 GMT

Hi Jonty

Your SQL query will be something like; where...:

"ObjectID" = (select min("ObjectID") from Mytable) and "Otherfield" = 0

Depending on the host database you're working in the syntax may need to be tweaked.
See the help on subqueries.

Regards

Re: SQL Select Query

JontyKnox — Thu, 01 Dec 2011 13:36:33 GMT

Hi Bruce,

So I constricted this from what you gave me, I've tweaked the database slightly since my last post, but the statement accurately reflects what I want to do:

SELECT MAX( "Departures" ) FROM SORT AND "Boolean" =0

However, when I verify the statement it still gives me the 'Invalid SQL statement was used' (twice). Any advice what I've messed up with the syntax?

Thanks, Jonty

Re: SQL Select Query

BruceHarold — Thu, 01 Dec 2011 13:47:19 GMT

Hi Jonty

Is "SORT" a reserved word in your DBMS?

Also, the parenthese around the subquery (select...) will be required.

Cheers

Re: SQL Select Query

JontyKnox — Thu, 01 Dec 2011 14:06:39 GMT

Thanks for the quick reply. SORT is the table name and is not a reserved word in my DBMS.

I just broke the query down to basics and the Boolean part works, as does the 'departures' section- only if I change it from Max to some other function such as >10 however.

This leads me to think that perhaps the problem is with the MAX function?

Cheers,

Re: SQL Select Query

BruceHarold — Fri, 02 Dec 2011 14:08:29 GMT

Jonty

What is your host DBMS, or are you using file GDB? Email me at bharold@esri.com and i'll determine the correct syntax.

Regards