Query Help
-
Hello,
I am attempting to run the following query..
select DM.DOCNUM, DM.VERSION, G.U_G, G.USER_GP_ID
from GetWindowsACL_Chicago G
join MHGROUP.DOCMASTER DM on G.Matter = DM.C2ALIAS
where G.U_G is not NULL
and G.PRJ_ID is not NULL
and G.ALLOW_DENY = 'Allow'
and DM.C12ALIAS = 'CHICAGO_GROUP5_PART1'
and DM.DOCNUM+'-'+G.USER_GP_ID not in
(select CAST(DOCNUM as VARCHAR)+'-'+CAST(USER_GP_ID as VARCHAR) from MHGROUP.DOC_ACCESS)
But, receive the following error..
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
Any help would be greatly appreciated.
Thank you!
-
Check the data types of the fields you are comparing, maybe the join fields. I'd focus on "G.Matter = DM.C2ALIAS" and "DM.DOCNUM+'-'+G.USER_GP_ID " sections of your query first.
What is the Data Types of Matter and C2Alias? Is one varchar and one float? You are also leaving implicit conversion in the "DM.DOCNUM+'-'+G.USER_GP_ID ". I personally prefer to never allow the engine to implicitly convert, always cast as it makes the code3 a little easier to read and understand when bug fixing.
-
Thanks for the response. Here are the fields listed..
G.Matter - varchar
GM.C2alias - nvarchar
DM.DOCNUM - float
G.USER_GP_ID - varchar
Does this help?
Thank you!
-
Have you considered using the EXISTS operator instead IN and avoid the concatenation?
...
AND NOT EXISTS (
SELECT *
FROM MHGROUP.DOC_ACCESS AS A
WHERE A.DOCNUM = DM.DOCNUM AND A.USER_GP_ID = G.USER_GP_ID
);
I wonder if you can share some insight about why using floating number for column [DOCNUM].
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply