Aditi Goswami · Jun 14, 2017

SQL Query for Range


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?



0 406
Discussion (9)8
Log in or sign up to continue

I tried using between but got the same result.

This will have the same problem.

I agree with Vitaliy.  You should look at the index, assuming there is an index on A and make sure it has all the values your expect.

if the index has all the rows and the query is not returning them it is a Bug that ISC needs to look into ASAP.

Using %IGNOREINDICES helped!

Thank you all :)

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.

...or did you already have data in the table when you added the index on A? Assuming that some or all of the pre-existing data is in your WHERE range, that would explain your problem, and as others have commented, an index rebuild is the remedy.


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..