Where clause syntax

Todd Sales

January 20, 2012 07:16

While I've been successful in building a few workflow based searches, I having a couple that fail to return results properly. 

I created a simple subdivision workflow that prompts for a text (name) entry, then queries the feature class to find matching names. But it returns all subdivision names. I've used various iterations of the where clause -

      "SUBNAME LIKE '%" + GetSub + "%'"

- tried adding parens around the quotes & wildcard characters ( "SUBNAME LIKE ('%" + GetSub + "%')"   )- no change

- tried adding single quotes around (both sides of) the wildcard characters, as I would do in a TSQL statement ( "SUBNAME LIKE  '%'" + GetSub + "'%'" ) - no change

- tried adding GetSub.ToString() - breaks the workflow with "Object reference not set to an instance of an object".

To troubleshoot, I created a variable with the WhereClause and an Alert to display contents of the string after the Query task. (did this also with the input text). The output displays SUBNAME LIKE "% %'    (which would explain all subdivisions being in the results set)

The other workflows I've built successfully are using similar methods (one using '=', the other 'LIKE') so I don't think it's a scope issue for the variable (plus, the entered value is displayed correctly in its own alert step...)

Any ideas on what I'm doing wrong here? 

Many thanks.

0

• 

  Ryan Cooney

  January 20, 2012 07:48

  Hi Todd,

  If GetSub.ToString() gives you an "Object reference not set to an instance of an object" error it means that your GetSub variable is null. Have a look at where that variable gets assigned.

  --Ryan

  0
• 

  James Landwehr

  January 21, 2012 01:43

  Hi Todd,

   

  This is an equivalent for what we use as an expression and it seems to return suitable results.

   

  "SUBNAME like '" &"%" & GetSub &"%'"

   

  Thanks,

  Jim

  0
• 

  Todd Sales

  January 25, 2012 02:11

   

  Thanks for the responses. The feedback seems to help substantiate my suspicions of the underlying problem, while not resolving it. It appears the where clause is not being interpreted correctly (i.e. null), so the LIKE statement returns all features. This occurs for multiple FC attributes (in different search workflows) and with different text strings/entries tested. It does not appear to be a case-sensitivity issue. BTW, it seems inconsistent, as I've built another search workflow using a LIKE statement in the where clause, and it functions as expected. Just trying to figure out what's different between them..???..

  0
• 

  Permanently deleted user

  January 26, 2012 04:53

  I was having a similar problem when using the value entered by the user. 

   

  I had the variable as string, but the search was failing.

  Once changed the variable for the user entered value to an OBJECT and in the Where clause convert the object to string it worked.

  Try this where clause "SUBNAME like '%" & GetSub.ToString() &"%'"

  0

Please sign in to leave a comment.