topic Re: Help with SQL query in Enhanced Search Widget in ArcGIS Viewer for Flex Questions

Help with SQL query in Enhanced Search Widget

Juan-CarlosTorres — Tue, 09 Sep 2014 18:26:52 GMT

I am trying to modify the input value in the Enhanced Search Widget using SQL. I have tested the SQL expression in the ArcMap field calculator and it works but it fails inside the Enhanced Search Widget.

I have a field called MapTaxlot where the values have random spaces. Some of the values have 1, 2, o 3 spaces.
I want the users to be able to type the field value with spaces and modify the input value to eliminate the spaces and query a field where the values have no spaces.
The reason for this is because when we try to use field values with spaces the query fails.

The SQL query to eliminate the spaces is: replace([MapTaxlot]," ", "")
When I combine the above SQL query with the value input in the Enhanced Search Widget it doesn't return a result.
Here is my combined query:

<expression alias="Taxlot" textsearchlabel="Enter the taxlot number below"> <values> <value>Taxlot_Num like ('%replace([value," ", "")%')</value> </values> </expression>

I hope Robert or somebody else could give a clue about what is happening. Thank you.

Re: Help with SQL query in Enhanced Search Widget

JeffWard — Tue, 09 Sep 2014 18:50:54 GMT

You need a closing bracket on [value

Taxlot_Num like ('%replace([value]," ", "")%')

Re: Help with SQL query in Enhanced Search Widget

JeffWard — Tue, 09 Sep 2014 19:38:51 GMT

On second thought, this is probably better:

Taxlot_Num like replace('%[value]%',' ', '')

Re: Help with SQL query in Enhanced Search Widget

Juan-CarlosTorres — Tue, 09 Sep 2014 20:05:57 GMT

Thank you Jeff.

The first one executes but it doesn't return anything.

The second one gives an error message.

I am pretty sure is the syntax because I am able to eliminate the empty spaces using the query in the field calculator.

Re: Help with SQL query in Enhanced Search Widget

RobertScheitlin__GISP — Tue, 09 Sep 2014 20:17:42 GMT

Juan,

I believe the issue you are running into is because Flex uses Replace as a keyword Just like SQL does and that is a conflict. I will have to do some verifying but I think I have looked at this exact situation before for another forum question.

Robert

Re: Help with SQL query in Enhanced Search Widget

RobertScheitlin__GISP — Tue, 09 Sep 2014 20:26:20 GMT

Juan,

So the issue is ArcGIS Server does not support the SQL syntax. You can test this by attempting the query that you have working in ArcMap and trying to use that in your Map Service REST endpoint form.

Example:

http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Louisville/LOJIC_LandRecords_Louisville/MapServer/2/query?text=&geometry=&geometryType=esriGeometryPoint&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&objectIds=&where=ZONING_CODE+LIKE+REPLACE%28%27%25O+R+1%25%27%2C%22+%22%2C+%22%22%29&time=&returnCountOnly=false&returnIdsOnly=false&returnGeometry=false&maxAllowableOffset=&outSR=&outFields=*&f=html

Re: Help with SQL query in Enhanced Search Widget

RobertScheitlin__GISP — Tue, 09 Sep 2014 20:28:13 GMT

Juan,

The only way I know to get around this is to change the source code to doe the REPLACE in Flex instead of trying to pass it to the server to do. Let me know if you have the ability to recompile the source and I will point you in the right direction.

Re: Help with SQL query in Enhanced Search Widget

Juan-CarlosTorres — Tue, 09 Sep 2014 20:33:31 GMT

Thank you Robert,

I am currently using the compiled version of your widget and the ArcGIS Viewer for Flex.

I have no flex compiler available