Query returning different number of results....

[Ya5irHa55an]

Hi, I hope someone can shed some light on the following problem:

I have a query of a few fields, which returns around 28'000 results. When I add a few more fields and run it again, it now only returns around 2000- why the change, I have not adjusted the criteria at all.

Could it be something to do with not having enough memory/hard drive space to complete the whole query (although i dont get any error message).

 

[skrandel]

You have to look at the SQL code it's generating. By returning less rows, this usually means that a new join has been added to the query which reduces the volume of data. This would mean that the "new" table in the query doesn't have match up with the original one.

For example, let say your first query returned 28,000 customers. You then add Invoices Amount to the query. You're no longer going to get 28,000 rows. You're only going to get invoices for customers who actually have Invoices. That should be substantially less rows.

Another possibility is a universe issue whereby someone has put a WHERE clause on the object you added.

Without seeing the SQL, before and after, it's impossible to tell you what's going on.

But, it definitely is not a hardware/memory issue.

Steve Krandel
Symantec

 

[Ya5irHa55an]

Thanks for your reply Steve- I checked the SQL and realised you are right!

 

[jabedin]

This looks the new field added to the report is coming from a new table with a one-to-one join. Check this and if required change the join to out-join if that is your requirement.