A strange issue!The count of queries does not match data.

User 14060732

I used SQLSERVER 2012,today when I query data,I found a very strange thing. As when I used the sql:

select count(1) as num from(select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx') )t

it returns 22 items but when I use statement query in parentheses:

select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx')

it returns 20 items how can this possible?I'm confused.

Maciej Los

Quote:

select count(1) as num from(select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx') )t

it returns 22 items

As to the semantic used in your statement: it does NOT return 22 items. It returns a number which should be equal to the count of records returned by the view. As to the strange issue... I can't reproduce your issue, but i can recommend to test this statement:

SELECT COUNT(*) --or COUNT(1), because both statements are equal!
FROM View_paymentApproval v1
WHERE EXISTS (
SELECT 1
FROM payment_Approval
WHERE contractNo=v1.contractNo AND payNo=v1.payNo AND SendSAPStatus='0' AND v1.approvalManCode='zhouyx'
)

Richard Deeming

When you have a query that involves multiple tables, it's a good idea to prefix every column with the table name / alias:

SELECT *
FROM View_paymentApproval v1
WHERE Exists
(
SELECT 1
FROM payment_Approval a1
WHERE a1.contractNo = v1.contractNo
And a1.payNo = v1.payNo
And a1.SendSAPStatus = '0'
And v1.approvalManCode = 'zhouyx'
)

Not only does it make it easier to work out which table the column comes from, it can also prevent bugs. For example, if the column contractNo existed in View_paymentApproval, but not in payment_Approval, then:

a1.contractNo = v1.contractNo

would produce an error, whereas:

contractNo = v1.contractNo

would produce incorrect results, since it would be equivalent to:

v1.contractNo = v1.contractNo

which would always be true.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

User 14060732

it's right.Thank you,dude.The column 'contractNo' has null value.