select sr.RECID [Role Id], case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Role name]
from AXPRDDB_model.dbo.SecurityRole sr with (nolock)
left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us'
--List of users with roles select sur.USER_ [User Id], sr.AotName, case when left(sr.Name, 1) = '@' then xr.Text else sr.NAME end as [Name], sur.VALIDFROM, sur.VALIDTO from AXPRDDB.dbo.SECURITYUSERROLE sur with (nolock) inner join AXPRDDB_model.dbo.SecurityRole sr with (nolock) on sur.SECURITYROLE = sr.RecId left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(sr.NAME, 1) = '@' then (case when cast(SUBSTRING(sr.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(sr.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us' --List of users with duties select t.AOTNAME, case when left(t.Name, 1) = '@' then xr.Text else t.NAME end as [Role name], case when left(t.DESCRIPTION, 1) = '@' then xrd.Text else t.DESCRIPTION end as [Role name] from SecurityRole s with (nolock) inner join SecurityRoleTaskGrant g with (nolock) on s.RECID = g.SECURITYROLE inner join SecurityTask t with (nolock) on g.SECURITYTASK = t.RECID left join AXPRDDB_model.dbo.ModelElementLabel xr with (nolock) on (case when left(t.NAME, 1) = '@' then (case when cast(SUBSTRING(t.NAME, 5, 7) as int) = xr.LabelId and SUBSTRING(t.NAME, 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1 and xr.Language = 'en_us' left join AXPRDDB_model.dbo.ModelElementLabel xrd with (nolock) on (case when left(t.DESCRIPTION, 1) = '@' then (case when cast(SUBSTRING(t.DESCRIPTION, 5, 7) as int) = xrd.LabelId and SUBSTRING(t.DESCRIPTION, 2, 3) = xrd.Module then 1 else 0 end) else 0 end) = 1 and xrd.Language = 'en_us' where s.AOTNAME = 'LedgerAccountant' |
1 comment:
Change all the left join to update inner join will speed up the performance.
Example:
CREATE TABLE #TEMP (USERID NVARCHAR(8), USERNAME NVARCHAR(40),AOTNAME NVARCHAR(255),NAME NVARCHAR(255))
INSERT INTO #TEMP (USERID,USERNAME,AOTNAME,NAME)
select sur.USER_ [User Id]
, U.NAME
, sr.AotName
, SR.NAME
from AXPRDDB.dbo.SECURITYUSERROLE sur with (nolock)
inner join USERINFO u with (nolock) on sur.USER_ = u.ID
inner join AXPRDDB_model.dbo.SecurityRole sr with (nolock) on sur.SECURITYROLE = sr.RecId
where sr.ISENABLED = 1
and u.ENABLE = 1
UPDATE TMP
SET NAME = XR.Text
FROM #TEMP TMP
INNER JOIN AXPRDDB_model.dbo.ModelElementLabel xr with (nolock)
on (case when left(TMP.NAME COLLATE Latin1_General_CI_AS , 1) = '@'
then (case when cast(SUBSTRING(TMP.NAME COLLATE Latin1_General_CI_AS , 5, 7) as int) = xr.LabelId and SUBSTRING(TMP.NAME COLLATE Latin1_General_CI_AS , 2, 3) = xr.Module then 1 else 0 end) else 0 end) = 1
and xr.Language = 'en_us'
WHERE LEFT(TMP.NAME,1) ='@'
select USERID,USERNAME,AOTNAME,NAME
from #temp
Post a Comment