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

Vote up!
Vote down!

Rating: 0

Comments: 5 Views: 158

Comments

Using %IGNOREINDICES helped!

Thank you all :)

Vote up!
Vote down!

Rating: 0

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.

Vote up!
Vote down!

Rating: 0

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

Vote up!
Vote down!

Rating: 0

5 answers

Answer

Try using BETWEEN.

Vote up!
Vote down!

Rating: 0

I tried using between but got the same result.

Vote up!
Vote down!

Rating: 0

This will have the same problem.

Vote up!
Vote down!

Rating: 0

Answer

I see nothing wrong with the query. Perhaps field A is interpreted as a string? Best to say which numbers are included/excluded.

Vote up!
Vote down!

Rating: 0

Answer
Vote up!
Vote down!

Rating: 0

Answer

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.

 

 

Vote up!
Vote down!

Rating: 0

Answer

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.

Vote up!
Vote down!

Rating: 0

Log in to answer