Where clause syntax using field name coming from a join, setting a variable

TracySchloss — Sun, 12 Dec 2021 08:10:41 GMT

This is for the where clause of a FeatureLayer.selectFeatures.

I have used a variable for a field name, but this is the first time I've tried to get this to work with a field name coming from a join to a SQL Server database table. I know the single quote/double quote looks strange, but hard coded, this is the only way it works. I assume this is because the field names from the join contain %, so I needed to surround the field name with extra punctuation.

I started off with this and it works:

var whereClause = '"MCDS_DATAWAREHOUSE.EDAPPGISSCHLDIRT.%SCHOOL_DIRECTORY_DISTRICT.DCOUNTY"' + " LIKE '%" + currentCounty + "%'";

The database is different from test to production and I need to set some variables instead.

var countyFieldName = "MCDS_DATAWAREHOUSE.EDAPPGISSCHLDIRT.%SCHOOL_DIRECTORY_DISTRICT.DCOUNTY";
var whereClause = countyFieldName + " LIKE '%" + currentCounty + "%'";

This doesn't work. When you put in a breakpoint, the syntax looks OK. I'm using LIKE because DCOUNTY has trailing blanks in it and I have no control over this agency's database.

I would actually prefer to just set the variable as MCDS_DATAWAREHOUSE.EDAPPGISSCHLDIRT.%SCHOOL_DIRECTORY_DISTRICT, which is the database name and just concatenate the field names. I have this set earlier for my infoTemplates. I couldn't wrap my head around this:

var distFieldPath = "MCDS_DATAWAREHOUSE.EDAPPGISSCHLDIRT.%SCHOOL_DIRECTORY_DISTRICT";
var whereClause = distFieldName + ".DCOUNTY LIKE '%" + currentCounty + "%'";

That's not quite right, because I need to punctuate the field name.

Re: Where clause syntax using field name coming from a join, setting a variable

TracySchloss — Sun, 12 Dec 2021 08:10:44 GMT

I got it to work. I had to add quotes as a string around the field name.

var distFieldPath = "MCDS_DATAWAREHOUSE.EDAPPGISSCHLDIRT.%SCHOOL_DIRECTORY_DISTRICT";
var fieldName = distFieldPath + ".DCOUNTY";
var whereClause = ' " ' + fieldName + ' " ' + " LIKE '%" + currentCounty + "%'";

topic Re: Where clause syntax using field name coming from a join, setting a variable in ArcGIS JavaScript Maps SDK Questions

Where clause syntax using field name coming from a join, setting a variable

Re: Where clause syntax using field name coming from a join, setting a variable