Distinct query on multiple tables

[TimLangner]

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

[LoganPugh]

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

[JakeSkinner]

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.

[JakeSkinner]

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.

[TimLangner]

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