Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Wrong result from count(*) Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2008 7:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2008 3:58 AM
Points: 2, Visits: 4
Hi all!
Im having a bit of a problem. I have to searvh a table to find how many rows who haves the same customerid(kunde_nr) as the customerid from a different table. So if my customer not exist in BOTH tables I do not want the customer in my result set. All the customers the result must have their customerid in both tables. Okay?
Heres how i have done it:
set @sql = @sql + ' and(select count(*) from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr) > 0'

If it counts more than 0 then the customer is valid.
The acctual sql statement is much bigger but that is the part making the trouble.
If I run this part alone in a query it returns the right result, but when it is used in the complete statement it returns wrong result. It returns +2 customers whom only exist in the A table and not in the CO55800T table as it must to be a part of the result.
Can anyone see what is wrong or where the problem might be?
Post #538442
Posted Tuesday, July 22, 2008 7:44 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
By the looks of your included T-SQL, you have gone about this in the wrong way.

Please post the entire SQL statement, the DDL for the tables, and some sample data if possible. Here are some tips for posting. If you follow these, you will get a MUCH better response.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #538479
Posted Tuesday, July 22, 2008 9:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 29, 2010 3:14 PM
Points: 71, Visits: 124
christian.dahl (7/22/2008)
Hi all!
Im having a bit of a problem. I have to searvh a table to find how many rows who haves the same customerid(kunde_nr) as the customerid from a different table. So if my customer not exist in BOTH tables I do not want the customer in my result set. All the customers the result must have their customerid in both tables. Okay?
Heres how i have done it:
set @sql = @sql + ' and(select count(*) from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr) > 0'

If it counts more than 0 then the customer is valid.
The acctual sql statement is much bigger but that is the part making the trouble.
If I run this part alone in a query it returns the right result, but when it is used in the complete statement it returns wrong result. It returns +2 customers whom only exist in the A table and not in the CO55800T table as it must to be a part of the result.
Can anyone see what is wrong or where the problem might be?


You might want to try to determine if using an EXISTS clause yields the same problem. If it does then you know the COUNT itself isn't the problem, but something else. It would look something like this:

SET @SQL = @SQL + ' AND EXISTS (SELECT 0x00 FROM C055800T WHERE KUNDE_NR COLLATE SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'

-Ben
Post #538544
Posted Tuesday, July 22, 2008 11:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369
Already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107116



N 56°04'39.16"
E 12°55'05.25"
Post #538657
Posted Wednesday, July 23, 2008 1:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2008 3:58 AM
Points: 2, Visits: 4
the exists generates the same fault. It is really strange. Two customers who does not exists in the second table where the count and exists statement has been tried out is being returned to my page. The only customers who should be returned should be those who exists in both tables.

This is how it looks like now:
select A.EKUNDEID,
RTRIM(A.KUNDENR) as kundenr,
RTRIM(B.ANAVNB) as navn,
RTRIM(B.ABYB) as postnummerby,
RTRIM(A.AEMAIL) as email,
A.SAPKUNDENR,
B.AKONTAKT
from JB53000T A, JB53100T B
where B.EKUNDEID = A.EKUNDEID'

if(@kundenr <> '')
set @sql = @sql + ' and A.kundenr like ''' + @kundenr + ''''
...
...
other statements to complete the search for the customers
...
set @sql = @sql + ' and exists(select * from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'
...

exec(@sql)
Post #539043
Posted Wednesday, July 23, 2008 1:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 PM
Points: 2,382, Visits: 3,369
I posted an answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107116



N 56°04'39.16"
E 12°55'05.25"
Post #539046
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse