Click here to Skip to main content
15,891,529 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Trying to find the problem in a large query I've narrowed the cause for unexpected results to a behaviour I do not comprehend.

Stage:

Simple table:

id	voucherid	orgid	perid	listtype
14	10		0	0		0
15	10		0	203986	        0
16	10		151735	0		0
19	9		0	203986	        1
20	9		151735	0		1	


Simple Query:

SQL
DECLARE @per int
DECLARE @org int
SET @per = 203986
SET @org = 1517351
SELECT * FROM [voucher2contact]
WHERE 
		[perid] <> @per AND 
		[orgid] <> @org AND
		listtype = 1 


Returns one line
id	voucherid	orgid	perid	listtype
20	9	        151735	0	1


-EDIT-

This is the corrected version:

-- listtype 0 ->Whitelisted
-- listtype 1 ->Blacklisted
-- Return not Blacklisted entries for @per + @org



VB
DECLARE @per int
DECLARE @org int
SET @per = 203986
SET @org = 151735

SELECT *
    FROM [ERA].[dbo].[voucher2contact]
    WHERE
    (
        (
            ([perid] <> @per AND [perid] > 0)
        )
        OR
        (
            ([orgid] <> @org AND [orgid] > 0)
        )
    )AND
    listtype = 1


Note to self : Do not ask stupid questions after more than 14hrs of coding ;)

THX for the help :D
Posted
Updated 9-Aug-12 21:14pm
v3

Are you sure your not missing one digit in Declare @org, it stops at 1 in your int and has 7 digits, while your table entery has 6 digits. So it looks like SQL is correct :)
 
Share this answer
 
Comments
Maciej Los 9-Aug-12 16:34pm    
An answer of politic. (i hope this is correct in english)
+5!
your table contains orgid 151735 and 0.
But in where clause you are fetching records where orgid<>1517351.

Definitely 151735<>1517351

I think you have to change the value in variable @org.
Instead of
SET @org = 1517351
use
SET @org=151735.

I would say definitely SQL server is not stupid.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900