topic Re: SQL Query Question in ArcGIS JavaScript Maps SDK Questions

SQL Query Question

RyanSellman — Tue, 30 Dec 2014 16:36:40 GMT

I'm trying to write a SQL query that takes values from two inputs. Below is what I have so far:

queryParcels.where = "PARID LIKE '" + "%" + dom.byId("parcelText").value  + "'" + " OR ADDR LIKE '" + "%" + dom.byId("addrText").value + "'";

The query executes fine but the issue I am having is when one of inputs is left empty. When this occurs, the query that's sent to the server has '%' in it which returns the maximum amount of records from the server. Even if I leave the wildcards out, I get similar results. For example, below are the the query string parameters when I enter a value for the parcel number, but leave the address value empty:

f:json
where:PARID LIKE '%6760621' OR ADDR LIKE '%'
returnGeometry:true
spatialRel:esriSpatialRelIntersects
outFields:PARID,ADDR,OBJECTID
outSR:102100

Is there a way to insert NULL into the where clause if an input is left empty?

Re: SQL Query Question

AngelynSnyder — Tue, 30 Dec 2014 16:56:56 GMT

Have you tried just adding

OR Table.PARID IS NULL OR Table.ADDR IS NULL

to your where clause, respectively, instead of wildcards?

Re: SQL Query Question

RobertScheitlin__GISP — Sat, 11 Dec 2021 13:24:03 GMT

Ryan,

Why not handle it with some conditional statements?

        queryParcels.where = "";
        if (dom.byId("parcelText").value !== "") {
          queryParcels.where +=  "PARID LIKE '%" + dom.byId("parcelText").value  + "'";
        }
        if (dom.byId("addrText").value === "") {
          if (queryParcels.where !== "") {
            queryParcels.where += "ADDR LIKE '%" + dom.byId("addrText").value + "'";
          } else {
            queryParcels.where += " OR ADDR LIKE '%" + dom.byId("addrText").value + "'";
          }
        }

Re: SQL Query Question

MichaelSchnack — Tue, 30 Dec 2014 17:54:25 GMT

I would test whether the fields were empty then construct the query based on the result. This would allow you to check for valid input (length or type) as well.

Re: SQL Query Question

RyanSellman — Tue, 30 Dec 2014 18:49:45 GMT

Robert,

Thanks for the response. I actually thought about handling it this way but didn't have any luck. Implementing your suggestion still didn't get it working. Maybe looking at the code in its entirety would help.

Here is the the application I have been using to test this:

Edit fiddle - JSFiddle

Any ideas as to what what I am doing wrong? I appreciate any help!

Ryan

Re: SQL Query Question

MichaelSchnack — Tue, 30 Dec 2014 19:11:27 GMT

I think you need the === on line 5 above to be !==

Also, add a console.log statement to see the query you've created.

Re: SQL Query Question

RobertScheitlin__GISP — Tue, 30 Dec 2014 19:17:21 GMT

Ryan,

I have a === instead of a !== My mistake.

Should be:

if (dom.byId("addrText").value !== "") {

Re: SQL Query Question

RyanSellman — Sat, 11 Dec 2021 13:24:06 GMT

Yep, that was it. Thanks so much to both of you for the help. Along with changing === to !== I made one more adjustment. Here is the working code:

queryParcels.where = "";  
  if (dom.byId("parcelText").value !== "") {  
   queryParcels.where +=  "PARID LIKE '%" + dom.byId("parcelText").value  + "'";  
  }  
  if (dom.byId("addrText").value !== "") {  
  if (queryParcels.where !== "") {  
  queryParcels.where += "OR ADDR LIKE '%" + dom.byId("addrText").value + "'";  
  } else {  
  queryParcels.where += "ADDR LIKE '%" + dom.byId("addrText").value + "'";  
  }  
  }