Query Not Working The Same Twice

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Not Working The Same Twice

Author

Topic

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-24 : 09:59:33

I created a query to pull a batch of client data from a particular day. I pulled the information yesterday and it returned 378 rows. I extracted that batch information from the system and converted it to an excel file. Then today, I ran the exact same query and it only returned 371 rows. For some reason 7 rows were absent. I extracted the file from the second day and compared it to the first day.

In comparing the two files I determined the 7 rows that were missing. Trying to determine why they were missing I manually went into the tables to see what changed with those rows to cause them to be absent the second day. After investigation, nothing had changed. The rows still met the parameters of the query but for some reason where not pulled on the second day.

How can this happen? What happens when the query simply won't return the results it is supposed to.

Here's the query. Pretty simply little query. It won't pull the same information from two days in a row.

SELECT dbo.Policy.policyID, dbo.Policy.policyNum, dbo.Policy.policyType, dbo.Policy.status, dbo.Policy.effectiveDate, dbo.Policy.expirationDate, dbo.Policy.cancelledDate,
dbo.Policy.insuredID, dbo.Insured.fname1, dbo.Insured.lname1, dbo.Policy.companyID, dbo.Policy.stateID, dbo.Policy.duplicate, dbo.Policy.addDate,
dbo.Policy.stopCancelDate, dbo.Policy.paymentPlanID, dbo.Policy.appQuoteTotal, dbo.policy.usersid, dbo.Policy.applicationUsersID, dbo.Policy.submissionUsersID

FROM dbo.Policy INNER JOIN
dbo.Insured ON dbo.Policy.insuredID = dbo.Insured.insuredID

WHERE (dbo.Policy.addDate > '2011-02-22') AND (dbo.Policy.addDate < '2011-02-23') and applicationusersid <> '1'

The insuredID is the exact same as yesterday.

The addDate is the exact same as yesterday.

The applicationusersid is the exact same as yesterday.

And ignoring the day it was pulled, the missing rows meet all the criteria: between the desired dates and do not have an applicationusersid of 1.

HOW CAN THIS HAPPEN?!?!?

JG777

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-24 : 10:47:58

Have you checked the Insured Table?

What is the count when you LEFT JOIN instead of INNER JOIN?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 10:54:15

replace the whole where clause with

Where insuredID in (<the insuredID of those missing 7 rows>)

check if this returns any row, if yes,

then
Where insuredID in (<the insuredID of those missing 7 rows>) And AddDate>'2011-02-22'

if worked then
Where insuredID in (<the insuredID of those missing 7 rows>) And AddDate>'2011-02-22' and AddDate<'2011-02-23'

if worked then
Where insuredID in (<the insuredID of those missing 7 rows>) And AddDate>'2011-02-22' and AddDate<'2011-02-23' and applicationusersid <> '1'

One of them should fail :) once you get the problem it would be easier for you to resolve

Cheers
MIK

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-24 : 11:17:47

I just manually checked the Insured table and it has not changed. The same InsuredID still match to the same fname1 and lname1.

I performed a Left Join and it returned 371 rows. I also performed a right join and it returned 371 rows.

JG777

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-24 : 11:36:05

MIK 2008 -

1. Where replaced with the InsuredID of missing 7 rows. There are only 3 InsuredIDs that match to those 3 rows.

Result: 12 Rows (However, it will not return a single one of the missing rows.)

2. Where insuredID in (<the insuredID of those missing 7 rows>) And AddDate>'2011-02-22'

Result: 12 Rows (However, it will not return a single one of the missing rows.)

3. Where insuredID in (<the insuredID of those missing 7 rows>) And AddDate>'2011-02-22' and AddDate<'2011-02-23'

Result: 11 Rows (However, it will not return a single one of the missing rows.)

4. Where insuredID in (<the insuredID of those missing 7 rows>) And AddDate>'2011-02-22' and AddDate<'2011-02-23' and applicationusersid <> '1'

Result: 11 Rows (However, it will not return a single one of the missing rows.)

Interestingly, I replaced the where clause to pull the missing PolicyIDs and it will not return anything. I then ran the following Query for each of the missing missing rows.

Select *
from Policy
Where PolicyID = <a single missing policyID>
---- repeated for each missing PolicyID

The result is nothing. Not a single row. However, I can manually go into the Policy table and find each of the missing PolicyIDs. It appears that SQL has simply decided to ignore those PolicyIDs. Is this a bug? Is there a fix?

JG777

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 11:36:10

ummm what is the datatype conditional column ... PolicyID ?

Can you exactly post the query as you executed?

Select *
from Policy
Where PolicyID = <a single missing policyID>

Cheers
MIK

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-24 : 11:52:49

I cannot type the PolicyID number. However, it is a the Primary Key for that Table. It is a number that simply counts up as each new entry occurs.

JG777

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 12:06:32

ok then rather investigating you whole query just investigate the problematic portion

. Also from this practice you may come to know that its not necessary an object is same as it looks so try avoiding manual checking .. by the way I suspect there is difference in the data of your column and the data you provide as parameter at the right .. now what would be the difference, is upto you to to investigate

Also, tell me something are you using SQL server as you database repository? or any other product? Because i can see that the way you are provided column names in your Select Statement in your original post is not the Syntax of SQL Server ... e.g. dbo.Policy.PolicyID

Cheers
MIK

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-24 : 12:45:32

Ok problem solved. It appears that entries are removed from the Policy tables when certain criteria are true. The entries are removed and put into a separate Log file. I did not shut down my computer from last night or close the Policy table since yesterday. Therefore, the cached policy table I was looking at displayed entries that were no longer in the table. The query was returning results from the actual table that didn't have the entries anymore.

JG777

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-24 : 16:04:39

This is probably irrelevant at this point, but I am using Microsoft SQL Server 2005. The syntax dbo.Policy.PolicyID is generated when I go into the design view of the query builder. Instead of typing out all the columns I want, I just click them and then cut and paste as needed.

JG777

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources