New post
Question
· Nov 4

Select query not returning all results

#Caché #InterSystems IRIS #InterSystems IRIS for Health

I'm running a query, joining nine tables together. When I run it with a SELECT COUNT(*) I get about 200,000 rows. When I run the SELECT with the columns I want, I get about 2,700 rows. Is there something inherent with the queries that limits the result set size, or anything I can do to make sure I'm getting back all the results? Rather, than trying to run it about 100 times limiting all previously gotten accounts.

Discussion (2)2
Log in or sign up to continue
Stephen Canzano · Nov 4

Might you be able to share the 2 sql statements and the query plans?

If you are running the queries in the System Management Portal there is a limit that you could set 

but I dont imagine that is the issue.

Where possible a COUNT(*) query could chose to use your indices and in theory your indices might be different from your table data but that would be highly suspicios under normal circumstances as the writing to the table and the indices are in a database transaction so it should have all been written or not.  

To your specific question

Is there something inherent with the queries that limits the result set size, or anything I can do to make sure I'm getting back all the results? Rather, than trying to run it about 100 times limiting all previously gotten accounts.

Again if you are running via the System Management Portal there are times I run the query with a limit of 100 for example and once the results are presented I select the Print hyperlink to run the entire query results to a file

0 0
Robert Stephens · Nov 4

I'm using DBeaver to access the database and run my queries. I found the issue though; apparently I was missing the error warning, but it was throwing an error on a date field manipulation I was doing, and killing the query at that step.

I'm taking the date of birth then using that to get the first day of the month they were born in, and the last day of the month they were born in plus different numbers of years. To make some age ranges. Apparently some of the records didn't of a date of birth, so when it hit that it errored out on my field to manipulate the date. So I had to put in a CASE statement to catch if the field was NULL and now the query is returning all results.

That explains why replace the select statement with just the COUNT(*) worked, since it removed that date field manipulation that was giving the problem.

Issue is resolved.

1 0