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