topic Re: Query.Where clause in Data Management Questions

Query.Where clause

WilliamMeredith — Wed, 25 Feb 2015 18:31:45 GMT

The below statement functions fine in a mapserver query:

query.where = "STRU_NAME = '" + structure+ "'";

I'd like to switch the " = " to a like statement. I can't seem to get this statement to return any data:

query.where = "STRU_NAME like '" + structure+"'";

Any suggestions? I'm fairly new to development, probably something easy.

Re: Query.Where clause

RobertScheitlin__GISP — Wed, 25 Feb 2015 18:35:41 GMT

William,

This should work:

query.where = "STRU_NAME like '" + structure+ "'%";

You need a wild card in the SQL

Re: Query.Where clause

WilliamMeredith — Wed, 25 Feb 2015 18:55:35 GMT

Thanks Robert. I had tried similar syntax changes like you mention but still have no luck, no data gets returned.

Here is the URL (part of it) that with the modification you mention:

query?f=json&where=STRU_NAME%20like%20'wind'%25&returnGeometry=false

This is the original (=, returns data on exact match).

query?f=json&where=STRU_NAME%20%3D%20'wind'&returnGeometry=false

Looks good to me, not sure where the hangup is.

Re: Query.Where clause

RobertScheitlin__GISP — Wed, 25 Feb 2015 19:05:21 GMT

William,

Sorry I had my % in the wrong place (% then single quote).

query.where = "STRU_NAME like '" + structure+ "%'";

Re: Query.Where clause

WilliamMeredith — Wed, 25 Feb 2015 19:10:19 GMT

Robert,

Thanks! Really appreciate your help (the correction above is working great).

Billy

Re: Query.Where clause

FilipKrál — Wed, 25 Feb 2015 19:10:29 GMT

Hi,

Some time ago I asked a similar question and I am guessing the same answer applies to you.

Can 10.2 answer Oracle where clause?

Statements with LIKE operator are not standardized queries and they are disabled in 10.2+ by default.

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

Hope this explains the issues you are having.

Filip.

Re: Query.Where clause

FilipKrál — Wed, 25 Feb 2015 19:11:44 GMT

What? How can it work?!? Have you disabled the requirement for standardized queries or does it work out of the box?

Filip.

Re: Query.Where clause

WilliamMeredith — Wed, 25 Feb 2015 19:30:07 GMT

Filip,

Thanks for the tip, I'll check this out when I have a moment.

Robert,

One last question... if I were to want to add an AND clause to this could you point me in the right direction?

query.where = "STRU_NAME like '" + stateName+ "%' and BMP_TYPE like '" + bmpType+ "%'";

Re: Query.Where clause

WilliamMeredith — Wed, 25 Feb 2015 19:32:33 GMT

nevermind... answered my own question in that post. should have tried it first