SQL Query for Range

Question

Question

Aditi Goswami · Jun 14, 2017

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

John Murray · Jun 16, 2017

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.

0 0

score 0 · Answer 1 · 2017-06-14T07:48:39-04:00

Rubens Silva · Jun 14, 2017

Try using BETWEEN.

0 0

score 0 · Answer 2 · 2017-06-14T08:13:40-04:00

Aditi Goswami · Jun 14, 2017

I tried using between but got the same result.

0 0

score 0 · Answer 3 · 2017-06-14T08:13:52-04:00

Kyle Baxter · Jun 14, 2017

This will have the same problem.

0 0

score 0 · Answer 4 · 2017-06-14T08:02:33-04:00

Vitaliy Serdtsev · Jun 14, 2017

Do make re-indexing: How to build indices for a class if you add an index after creating records

0 0

score 0 · Answer 5 · 2017-06-14T08:41:45-04:00

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.

score 0 · Answer 6 · 2017-06-16T03:06:37-04:00

Aditi Goswami · Jun 16, 2017

Using %IGNOREINDICES helped!

Thank you all :)

0 0

score 0 · Answer 7 · 2017-06-16T08:11:53-04:00

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

score 0 · Answer 8 · 2017-06-18T06:21:15-04:00

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.