topic Re: Distinct query on multiple tables in Python Questions

Distinct query on multiple tables

TimLangner — Thu, 29 Sep 2011 14:10:12 GMT

Hi there

How would I go about doing a distinct query on multiple tables using Python? I am working in ArcMap 10.0.2 with a geodatabase accessed through ArcSDE 9.3.1 and held on an Oracle 10g database.

Kind regards

Tim

Re: Distinct query on multiple tables

LoganPugh — Thu, 29 Sep 2011 15:42:00 GMT

Depends what you are trying to do, but you could try ArcSDESqlExecute: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//000v00000057000000.htm

Re: Distinct query on multiple tables

JakeSkinner — Fri, 30 Sep 2011 10:46:48 GMT

Hi Tim,

Here is an example on how to do this using the 'ArcSDESqlExecute' function, as lpugh01 mentioned:

import arcpy

sdeConn = arcpy.ArcSDESQLExecute("GIS01", "sde:oracle11g:orcl", "", "vector", "vector")

tableList = ["School_District, "Building_Sites", "Parcels"]

for table in tableList:
    print table + " contains the following cities:"
    try:
        print sdeConn.execute('select distinct City from ' + table)
    except AttributeError:
        print "None, field does not exist"

The above code will run through each table and search for a field named 'City' and return the distinct values.

Re: Distinct query on multiple tables

JakeSkinner — Sat, 11 Dec 2021 23:09:35 GMT

Hi Tim,

Here is an example on how to do this using the 'ArcSDESqlExecute' function, as lpugh01 mentioned:

import arcpy

sdeConn = arcpy.ArcSDESQLExecute("GIS01", "sde:oracle11g:orcl", "", "vector", "vector")

tableList = ["School_District, "Building_Sites", "Parcels"]

for table in tableList:
    print table + " contains the following cities:"
    try:
        print sdeConn.execute('select distinct City from ' + table)
    except AttributeError:
        print "None, field does not exist"

The above code will run through each table and search for a field named 'City' and return the distinct values.

Re: Distinct query on multiple tables

TimLangner — Wed, 12 Oct 2011 07:43:10 GMT

Thank you for your comments.

That is just what I was looking for. I assume could I then create a view of the results which is accessible in ArcMap, although it wouldn't be possible to update it but then it isn't possible to do that is a Make Query Table, which I have been using.

I actually did the distinct work in Excel this time and then used the Make Query Table to join this new table. I then exported this new table because it isn't possible to do a relate on a Make Query Table. For sound reasons I am sure but then I fail to see the sound reasons why a make query table can't be edited in the first place. Hopefully ESRI will add such a feature in a later release of ArcMap. By importing this new table I could do a relate to get the rows that did not match and I could then manually update them. Quite long winded but it at least allowed me to do what I required.

Kind regards

Tim