Cannot Query on Field in IRIS
I have a persistent class that represents cities across the United States. It is below, but basically has a City Id, Name, Lat, Lon and a few other unimportant fields for this issue. Anytime I attempt to query on the Latitude or Longitude it immediately returns no results. My first thought was that it was a casting issue so I tried casting both sides to floats, ints, even strings and in all cases it immediately comes back with no results. I then decided to cast it to a string and attempt a like statement thinking it might be something about how floats are handled, but still no joy. Any ideas on why I cannot query on these fields? More details are below.
Property CityId As %Library.Integer;
Property City As %Library.String(MAXLEN=500);
Property State As %Library.String(MAXLEN=2);
Property Country As %Library.String(MAXLEN=500);
Property Longitude As %Library.Float;
Property Latitude As %Library.Float;
Property USCRNName As %Library.String;
Property Status As %Integer;
Property DateRecAdded As %Library.DateTime [InitialExpression = { $ZDT($SYSTEM.Util.UTCtoLocalWithZTIMEZONE($ZTIMESTAMP), 3, 1, 0) }];
Index CityId ON CityId;
Index State ON State;
Index Longitude ON Longitude;
Index Latitude ON Latitude;
Index Status On Status;
Sample Data
SELECT CityId, Latitude, Longitude FROM MML.Cities
4070245 31.21073 -85.484657
4344544 31.11685 -93.183502
4215307 31.313 -81.55899
5285039 31.386209 -110.288139
4673179 31.40266 -98.081139
4047656 34.037609 -94.107697
5493998 34.58979 -105.28611
5815135 47.500118 -120.501472
5391891 34.106682 -117.806732
4056099 31.41683 -86.000221
Knowing 31.21073 is a valid Latitude I try the following query
SELECT CityId, Latitude, Longitude FROM MML.Cities WHERE Latitude = 31.21073
No results
SELECT CityId, Latitude, Longitude FROM MML.Cities WHERE CAST(Latitude as Float) = CAST(31.21073 AS Float)
No results
SELECT CityId, Latitude, Longitude FROM MML.Cities WHERE SUBSTRING(Latitude, 0, 3) = 31
No Results
SELECT CityId, Latitude, Longitude FROM MML.Cities WHERE CAST(SUBSTRING(Latitude, 0, 3) as Integer) = 31
No Results
Is it possible that you added index definitions after you filled the table with data?
If that is the case, then those indices are not built ("empty"), and so if the query uses them, then it returns no data. In such case you have to build your indices manually via ##class(MML.Cities).%BuildIndices().
Just tried this and it did in fact fix the issue. Never even thought about the fact that the indexes were put in place a few days after the table was populated. Thank you!