3

I am experiencing a weird problem while getting count from one table. The table name is Client and there is a varchar(200) column named CardNo. The database engine is Intersystems Cache and the querying syntax is SQL.

I execute queries below to get the count of Clients having/not having a CardNo. But getting the unexpected results as below.

select count(*) from Client
where CardNo is null
--Result: 38000

select count(*) from Client
where CardNo is not null
--Result: 78000

select count(*) from Client
--Result: 265000

The number of clients without CardNo is 38000. The number of clients with CardNo is 78000. The number of all clients in the table is 265000, which is not equal to 78000 + 38000. How could this happen? And according to the documents there should be no problem with my querying style http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_null

Ahmet
  • 85
  • 1
  • 8
  • Perhaps some other processing is inserting/updating/deleting when you run your queries. – Gordon Linoff Oct 10 '16 at 23:14
  • Thanks @GordonLinoff, I have checked the situation with the db admin but there were no other queries manipulating the records. – Ahmet Oct 11 '16 at 22:16

2 Answers2

5

Please make sure that your indices are up-to-date. Sometimes people change table definition and forget to build indices, so you only end up with indices for new data you inserted after changing index definitions.

You need to have up-to-date indices even if you don't index CardNo field, because SQL engine can choose to go through index global instead of extent ("main") global.

You can rebuild indices from management portal, or from terminal:

do ##class(your.class.name).%BuildIndices()
SSH
  • 2,533
  • 16
  • 21
  • Pretty sure this is the answer here. I've seen this behavior numerous times before. – joebeeson Oct 11 '16 at 01:02
  • Thanks @SSH , I strongly believe that this can be the reason of the issue. Unfortunately I am not permitted to run such query due to governmental policies. But I have opened a support ticket to db admins. I will update here as soon as I get the results. – Ahmet Oct 11 '16 at 22:27
0
SELECT SUM( CASE WHEN CardNo IS NULL THEN 1 END ) AS Null_Count,
       SUM( CASE WHEN CardNo IS NOT NULL THEN 1 END ) AS Not_Null_Count,
       COUNT( CardNo ) AS CardNo_Count
  FROM Client;

Try verifying your cardno counts to make sure your queries are doing it right. If you are running this query multiple times and if you see there is an increase in the counts then there should be a DML transaction running in the background.

Teja
  • 13,214
  • 36
  • 93
  • 155
  • Thanks @Teja, I have run the queries several times but the result is always same. Only a few new records increasing every day which is expected because of new Clients. – Ahmet Oct 11 '16 at 22:19