Using WhereClause in your QueryDef to create a QueryTable

Question

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!

Wolf · Answer

@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

Aashis · Answer

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]}"