| Author |
 Topic  |
|
|
dr223
Constraint Violating Yak Guru
359 Posts |
 Posted - 08/10/2012 : 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
|
Edited by - dr223 on 08/10/2012 07:40:31
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/10/2012 : 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
359 Posts |
Posted - 08/10/2012 : 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
|
Edited by - dr223 on 08/10/2012 07:46:25 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/10/2012 : 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. |
Edited by - nigelrivett on 08/10/2012 09:22:36 |
|
|
| |
Topic |
|
|
|
Results Not Correct!
