SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

easy_goer
Starting Member

21 Posts

Posted - 11/06/2013 :  23:57:13  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 11/07/2013 :  01:02:31  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 11/07/2013 :  01:26:28  Show Profile  Reply with Quote
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
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 11/07/2013 :  01:35:23  Show Profile  Reply with Quote
Thanks much!
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
420 Posts

Posted - 11/07/2013 :  01:56:26  Show Profile  Reply with Quote
w welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 11/07/2013 :  10:24:33  Show Profile  Reply with Quote
Hello. Is there any way to get this script to run faster? It's working, but taking a long time. Thanks
Go to Top of Page

Ifor
Aged Yak Warrior

583 Posts

Posted - 11/07/2013 :  11:10:35  Show Profile  Reply with Quote
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
	);
Go to Top of Page

easy_goer
Starting Member

21 Posts

Posted - 11/07/2013 :  23:01:52  Show Profile  Reply with Quote
Perfect. Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000