Question
· Jun 14, 2017

SQL Query for Range

Hello,

I have a question related to running an SQL query for range of data.

I am running a query like:

Select A,B from table_name where A>=12345 AND A<=12390

 - Where A and B are my two of the properties under the mentioned table/class definition

- A is an integer property

 Question: The result of the above query does not return me all the values between the mentioned range of integer though I do have all the values in that range. I see that some of the values are missing in result.

Any reason why this query won't work?

Any ideas?

 

Thanks,
Aditi

Discussion (9)3
Log in or sign up to continue

Then it definitely sounds like you need to rebuild indices. After doing that, please test again without %IGNOREINDICES and let us know if the results are correct. Then test again after your application has updated records for a while. If the difference appears again, either there's a bug that InterSystems needs to know about, or perhaps your app is updating globals directly using COS commands (i.e. not via Objects or SQL).

What is your $ZVERSION value? Perhaps you are running a version with a known problem in the area of index maintenance.

Aditi,

Two thoughts.. 

Firstly, you say A is defined as Integer - my experience shows, in cases where the data is seen as a "string" using ".. A>=12345*1 and A<=12390*1.." will ensure the 'numeric' values are used so a 'numeric' rather than 'character' comparison is performed 
Second, adding " order by A*1" on the end of the query will ensure that you see the list in actual numerically sorted order and unlikely to miss some values

Just a thought..

Rosti.