Results Not Correct! - SQL Server Forums

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

Results Not Correct!

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-10 : 07:28:37

Hi,

I have the following code,

SELECT     GPRDTech.gprdsql.TblPracDetails.prac_no, GPRDTech.gprdsql.TblPracDetails.GoldPracID, GPRDTech.gprdsql.TblPracDetails.practice_name, 
                      GPRDTech.gprdsql.TblPracDetails.prac_status, dbo.TblPracExclude.prac_enabled
FROM         GPRDTech.gprdsql.TblPracDetails LEFT OUTER JOIN
                      dbo.TblPracExclude ON GPRDTech.gprdsql.TblPracDetails.prac_no = dbo.TblPracExclude.prac_no
WHERE     (GPRDTech.gprdsql.TblPracDetails.prac_status <> dbo.TblPracExclude.prac_status) OR
                      (GPRDTech.gprdsql.TblPracDetails.practice_name <> dbo.TblPracExclude.prac_name) OR
                      (GPRDTech.gprdsql.TblPracDetails.GoldPracID <> dbo.TblPracExclude.GoldpracID)

It is working now; though, the problwm is in ;

GPRDTech.gprdsql.TblPracDetails.GoldPracID = values 1, 2, 3 for prac_no 10,11,12

dbo.TblPracExclude.GoldpracID = values NULL, NULL, NULL for prac_no 10,11, 12.

Why dont prac_no 10, 11, 12 dont come up in the output result, as surely, it fulfils the creteria GPRDTech.gprdsql.TblPracDetails.GoldPracID <> dbo.TblPracExclude.GoldpracID

Thanks

Many thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-10 : 07:40:35

depends on the format of the caracter string

convert(nvarchar(20),GPRDTech.gprdsql.TblPracDetails.GoldPracID) = dbo.TblPracExclude.GoldpracID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-08-10 : 07:46:08

Ok - Changed to as suggested and retained the nvarchar datatype;

 SELECT     GPRDTech.gprdsql.TblPracDetails.prac_no, GPRDTech.gprdsql.TblPracDetails.GoldPracID, GPRDTech.gprdsql.TblPracDetails.practice_name, 
                      GPRDTech.gprdsql.TblPracDetails.prac_status, dbo.TblPracExclude.prac_enabled
FROM         GPRDTech.gprdsql.TblPracDetails LEFT OUTER JOIN
                      dbo.TblPracExclude ON GPRDTech.gprdsql.TblPracDetails.prac_no = dbo.TblPracExclude.prac_no
WHERE     (GPRDTech.gprdsql.TblPracDetails.prac_status <> dbo.TblPracExclude.prac_status) OR
                      (GPRDTech.gprdsql.TblPracDetails.practice_name <> dbo.TblPracExclude.prac_name) OR
                      (CONVERT(nvarchar(50), GPRDTech.gprdsql.TblPracDetails.GoldPracID) <> dbo.TblPracExclude.GoldpracID)

GPRDTech.gprdsql.TblPracDetails.GoldPracID = values 1, 2, 3 for prac_no 10,11,12

dbo.TblPracExclude.GoldpracID = values NULL, NULL, NULL for prac_no 10,11, 12.

Why dont prac_no 10, 11, 12 come up in the output result, as surely, it fulfils the criteria GPRDTech.gprdsql.TblPracDetails.GoldPracID <> dbo.TblPracExclude.GoldpracID

Note: When I use the = instead of <> it pulls down records when GPRDTech.gprdsql.TblPracDetails.GoldPracID = dbo.TblPracExclude.GoldpracID but the other not equal doesnt work

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-10 : 09:21:19

Because 1 <> null returns null which is interpreted as false.
try
(
CONVERT(nvarchar(50), GPRDTech.gprdsql.TblPracDetails.GoldPracID) <> dbo.TblPracExclude.GoldpracID
or (GPRDTech.gprdsql.TblPracDetails.GoldPracID is null and dbo.TblPracExclude.GoldpracID is not null)
or (GPRDTech.gprdsql.TblPracDetails.GoldPracID is not null and dbo.TblPracExclude.GoldpracID is null)
)

or maybe
coalesce(CONVERT(nvarchar(50), GPRDTech.gprdsql.TblPracDetails.GoldPracID),'-1') <> coalesce(dbo.TblPracExclude.GoldpracID,'-1')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources