Query Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

easy_goer
Starting Member

21 Posts

Posted - 2013-11-06 : 23:57:13

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!

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-11-07 : 01:02:31

I guess


and DM.DOCNUM+'-'+G.USER_GP_ID not in

should be :


 CAST(DOCNUM as VARCHAR)+'-'+CAST(USER_GP_ID as VARCHAR)

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-11-07 : 01:26:28

like this:


and CAST(DM.DOCNUM as VARCHAR)+'-'+CAST(G.USER_GP_ID as VARCHAR) not in

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

easy_goer
Starting Member

21 Posts

Posted - 2013-11-07 : 01:35:23

Thanks much!

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-11-07 : 01:56:26

w welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

easy_goer
Starting Member

21 Posts

Posted - 2013-11-07 : 10:24:33

Hello. Is there any way to get this script to run faster? It's working, but taking a long time. Thanks

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-11-07 : 11:10:35

Try getting rid of the CASTs:


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 NOT EXISTS
	(
		SELECT 1
		FROM MHGROUP.DOC_ACCESS A
		WHERE A.DOCNUM = DM.DOCNUM
			AND A.USER_GP_ID = G.USER_GP_ID
	);

easy_goer
Starting Member

21 Posts

Posted - 2013-11-07 : 23:01:52

Perfect. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources