SQL Query Question

[RyanSellman]

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:

1. f:json
2. where:PARID LIKE '%6760621' OR ADDR LIKE '%'
3. returnGeometry:true
4. spatialRel:esriSpatialRelIntersects
5. outFields:PARID,ADDR,OBJECTID
6. outSR:102100

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

[AngelynSnyder]

Have you tried just adding 

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

to your where clause, respectively, instead of wildcards?

[RobertScheitlin__GISP · Accepted solution]

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 + "'";
          }
        }

[RyanSellman]

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

[MichaelSchnack]

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

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

[RobertScheitlin__GISP]

Ryan,

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

Should be:

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

[RyanSellman]

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 + "'";  
  }  
  }

[MichaelSchnack]

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.