Reload the DB and see if the problem goes away. Corrupt indexes can cause this kind of anomaly.
Dennis McGrath ________________________________ From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, November 03, 2010 12:40 PM To: RBASE-L Mailing List Subject: [RBASE-L] - SELECT counts Version: Turbo V-8 with latest update. I have a view that contains one "AS SELECT" query and one "UNION ALL SELECT" query. I encountered a puzzling situation with it when I queried for 2 CLM_ID values. CLM_ID is an INTEGER data type. The column is indexed. Example 1 gives an incorrect result using the "WHERE clm_id IN" statement. There are only 2 records but the command returns 3. Examples 2 through 5 are variations of the same statement and each one returns the correct answer. Why would Example 1 give an incorrect result? I triple checked the query to make sure it was logically correct. It was. Also puzzling is if I construct the view without the "UNION ALL SELECT" Example 1 returns the correct answer. Any thoughts? Mike Ramsour AK Steel Coshocton Works Example 1: Incorrect result SELECT COUNT(*) FROM reship_view WHERE clm_id IN (42723,42724) -- (Selecting or browsing shows 2 records for CLM_ID 42724) COUNT (*) ---------- 3 Example 2: SELECT COUNT(*) FROM reship_view WHERE clm_id IN (42724) COUNT (*) ---------- 1 Example 3: SELECT COUNT(*) FROM reship_view WHERE clm_id IN (42723) COUNT (*) ---------- 1 Example 4: SELECT COUNT(*) FROM reship_view WHERE clm_id = 42723 OR clm_id = 42724 COUNT (*) ---------- 2 Example 5: SELECT COUNT(*) FROM reship_view WHERE clm_id BETWEEN 42723 AND 42724 COUNT (*) ---------- 2 Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed and may contain privileged, proprietary, or otherwise private information. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message.
