topic Using WhereClause in your QueryDef to create a QueryTable in ArcGIS Pro SDK Questions

Using WhereClause in your QueryDef to create a QueryTable

tzz_12 — Tue, 18 Jan 2022 20:15:12 GMT

Hi Everyone,

I am trying to use a list of keys, mukeys, to search for these mukey within the mukey field in the component table and create a query table, CompTable. I keep getting an error, field not found at the line, WhereClause within the QueryDef. How do I write the WhereClause correctly using a the mukeys list? Here is my script below:

//there is more script above, but I just included the script section related to this question.

using (Row row = rowCursor.Current)
{
mukeyCount++;
var mukey = Convert.ToString(row["MUKEY"]);
mukeys.Add(mukey);

}

...

QueryDef queryDef = new QueryDef
{
Tables = "component",
WhereClause = "mukey= mukeys[mukeycount]", //error occurs here
SubFields = "component.cokey,component.mukey,component.comppct_r"
};

QueryTableDescription queryTableDescription = new QueryTableDescription(queryDef)
{
Name = "CompTable",
PrimaryKeys = geodatabase.GetSQLSyntax().QualifyColumnName("component", "cokey")
};

Table queryTable = geodatabase.OpenQueryTable(queryTableDescription);

Thanks everyone!

Re: Using WhereClause in your QueryDef to create a QueryTable

Aashis — Thu, 20 Jan 2022 13:30:24 GMT

WhereClause = "mukey= mukeys[mukeycount]", //error occurs here

Because the WhereClause is looking for the field name as mukeys[mukeycount] that is not available. I am guessing that you are trying to find a field name in mukeys[mukeycount] which can be fetched as:

WhereClause = $"mukey= {mukeys[mukeycount]}"

Re: Using WhereClause in your QueryDef to create a QueryTable

Wolf — Tue, 25 Jan 2022 15:30:01 GMT

@Aashis is correct your 'where' clause is not properly constructed. I think your intend was to construct a where clause using the 'in' clause, for example:

mukey in (100, 101, 205)

The 'in' clause allows you to specify a list of possible matches. Depending on the datatype of mukey you might have to surround the 'in' clause items in single quotes.

Here is a sample that worked for me:

protected override void OnClick() { var mukeys = new List<string>(); mukeys.Add("Test 1"); mukeys.Add("Test 3"); var inClause = string.Join("','", mukeys.Select(i => i.Replace("'", "''"))); _ = QueuedTask.Run(() => { QueryDef queryDef = new QueryDef { Tables = "testlines", WhereClause = $@"description in ('{inClause}')", SubFields = "objectid,description" }; System.Diagnostics.Trace.WriteLine($@"Where: {queryDef.WhereClause}"); var defaultGdb = new FileGeodatabaseConnectionPath(new Uri(CoreModule.CurrentProject.DefaultGeodatabasePath)); using (Geodatabase geodatabase = new Geodatabase(defaultGdb)) { QueryTableDescription queryTableDescription = new QueryTableDescription(queryDef) { Name = "testlines", PrimaryKeys = geodatabase.GetSQLSyntax().QualifyColumnName("testlines", "objectid") }; using (var queryTable = geodatabase.OpenQueryTable(queryTableDescription)) { using (var cursor = queryTable.Search ()) { while (cursor.MoveNext()) { using (var row = cursor.Current) { System.Diagnostics.Trace.WriteLine($@"Found: {row["objectid"]} {row["description"]}"); } } } } } }); }

The diagnostic output of this snippet looks like this:

Where: description in ('Test 1','Test 3')
Found: 9 Test 1
Found: 11 Test 3