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.
Author |
Topic |
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 05:17:34
|
Hi, I have a group called Leaders group. Users belonging to this Leaders group should be able to see only people information belonging to Leaders group. The following people belong to Leaders group[a,,c,d,e,f]. I need to display this for a SSRS report. Can anyone help me with the query. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 05:41:04
|
you need to use a filetr based on User!UserID. This will pass in userid corresponding to logged in user, check and see if they belong to leader group in query behind and show only the data for leaders group if they belong to it.I assume you've table storing Group Member details------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 05:51:03
|
Yes you are correct visakh16 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 05:57:44
|
then just do likeDECLARE @IsLeader bitSET @IsLeader= CASE WHEN EXISTS(SELECT 1 FROM GroupMember WHERE GroupName='Leader' AND UserID=@UserID) THEN 1 ELSE 0 ENDSELECTFROM TableWHERE UserName IN ('a,'c','d','e','f')OR @IsLeader=0 Set UserID parameter to take value from User!UserID------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 06:31:44
|
Initially the user logs in and where we query a table called "userprofile". This table has a column called user_Id which checks the username who logs in.Then we query a table called "GroupLookup" which has a column named People_Group which has leaders group in it.This table also has a column called "People_CD" and we compare this column to another table called Impacted_People which has same column. So we fetch the datas from these. |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 06:37:42
|
Here is my requirementInitially the user logs in and where we query a table called "userprofile". This table has a column called user_Id which checks the username who logs in. Then we query a table called "GroupLookup" which has a column named People_Group which has leaders group in it.This table also has a column called "People_CD" and we compare this column to another table called Impacted_People which has same column. So we fetch the datas from these. Ontop of this some people group present in People_Group column in GroupLookup table should belong to Leaders group. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 07:16:17
|
Apply same logic to your tables likeDECLARE @IsLeader bitSET @IsLeader= CASE WHEN EXISTS(SELECT 1 FROM GroupLookup gl INNER JOIN Impacted People ip ON ip.People_CD = g.People_CDINNER JOIN userprofile up ON up.User_ID = ip.User_IDWHERE People_Group ='Leader' AND up.UserName=@User) THEN 1 ELSE 0 ENDSELECT *FROM YourQueryTableWHERE UserName IN ('a,'c','d','e','f')OR @IsLeader=0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 07:45:33
|
quote: Originally posted by visakh16 Apply same logic to your tables likeDECLARE @IsLeader bitSET @IsLeader= CASE WHEN EXISTS(SELECT 1 FROM GroupLookup gl INNER JOIN Impacted People ip ON ip.People_CD = g.People_CDINNER JOIN userprofile up ON up.User_ID = ip.User_IDWHERE People_Group ='Leader' AND up.UserName=@User) THEN 1 ELSE 0 ENDSELECT *FROM YourQueryTableWHERE UserName IN ('a,'c','d','e','f')OR @IsLeader=0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogsHi VisakhI ran the query and get the error messageMsg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Group'.Msg 137, Level 15, State 2, Line 5Must declare the scalar variable "@User".Msg 102, Level 15, State 1, Line 10Incorrect syntax near '‘'.This is my query I made changes:DECLARE @IsCCSG bitSET @IsCCSG Group= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB _CDINNER JOIN USER_PRFL up ON up.User_Id = il.User_IDWHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 ENDSELECT *FROM BSP_LOB_GRPWHERE UserName IN (‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘Auto and Home’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MetLife Group Services’, ‘Group Life Products’, ‘WebTech’, ‘Long Term Care’, ‘Total Control Account’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘Marketing Services’)OR @IsCCSG Group=0
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 07:55:14
|
Group is a reserved word in SQLTry enclosing it within []DECLARE @IsCCSG bitSET @IsCCSG Group= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB _CDINNER JOIN USER_PRFL up ON up.User_Id = il.User_IDWHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 ENDSELECT *FROM BSP_LOB_GRPWHERE UserName IN (‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘Auto and Home’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MetLife Group Services’, ‘Group Life Products’, ‘WebTech’, ‘Long Term Care’, ‘Total Control Account’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘Marketing Services’)OR @IsCCSG Group=0 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 08:01:22
|
You Mean like thisDECLARE @IsCCSG bitSET @IsCCSG[]= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB_CDINNER JOIN USER_PRFL up ON up.User_Id = il.User_IDWHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 ENDSELECT *FROM BSP_LOB_GRPWHERE UserName IN (‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘A&H’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MGS’, ‘GLP’, ‘WebTech’, ‘Long Term Care’, ‘TCA’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘MKS’)OR @IsCCSG[]=0The thing is I have a group called CCSG Group in the BSP_LOB_GRP column |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 08:07:53
|
quote: Originally posted by vignesht50 You Mean like thisDECLARE @IsCCSG bitSET @IsCCSG[]= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB_CDINNER JOIN USER_PRFL up ON up.User_Id = il.User_IDWHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 ENDSELECT *FROM BSP_LOB_GRPWHERE UserName IN (‘MLCT’, ‘GenAm’, ‘NEF’, ‘Annuity’, ‘A&H’, ‘Annuity and Investments’, ‘Dental CORE’, ‘Disability’, ‘MGS’, ‘GLP’, ‘WebTech’, ‘Long Term Care’, ‘TCA’, ‘Critical Illness’, ‘Disability Xerox’, ‘TRICARE Xerox’, ‘MGS Xerox’, ‘WebTech’, ‘MKS Xerox’, ‘Dental Sykes’, ‘Life Sykes’, ‘Auto and Home Sykes’, ‘Auto and Home Sales’, ‘Life Middle Market’, ‘Dental Sales’, ‘Multi Product Enrollment’, ‘Critical Illness Sales’, ‘MKS’)OR @IsCCSG[]=0The thing is I have a group called CCSG Group in the BSP_LOB_GRP column
nope in this case dont put [] as its a variable nameI was thinking you've a column by name Groupyour earlier code's issue was space character in name of variable------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 08:16:19
|
Here is the one TABLES:User_Profile:User_Id User_FullName Email User_Group Group_Lookup: LOB_GRP LOB_CD LOB_TYPE_CD Impacted_LOB: Event_ID LOB_CD First user log-ins which is queried in User_Profile table. Then I have a group called CCSG Group in LOB_GRP column in Group_Lookup table. So now this CCSG Group has its respective LOB_CD in same table. Now I need to compare LOB_CD column of Group_Lookup and Impacted_LOB and display the result for that particular group. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 08:48:09
|
so how do relate between user_profile and other tables? is it based on User_Group = LOB_GRP ?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 08:56:43
|
Yes Visakh, exactly you are correct. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 09:07:04
|
then shouldnt your join be this?SET @IsCCSG[]= CASE WHEN EXISTS (SELECT 1 FROM BSP_LOB_Grp_Lookup gl INNER JOIN IMPACTED_LOB il ON il.BSP_LOB_CD = g.BSP_LOB_CDINNER JOIN USER_PRFL up ON up.User_Group = gl.LOB_GRPWHERE BSP_LOB_GRP ='CCSG Group' AND up.UserName=@User) THEN 1 ELSE 0 END ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-09 : 09:23:17
|
Yes. We need to relate like you told. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-09 : 12:59:51
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-10 : 01:41:41
|
quote: Originally posted by visakh16 welcomeHi Visakh,Here is my full thingHere is the one TABLES:User_Profile:User_Id User_FullName Email User_Group Group_Lookup: LOB_GRP LOB_CD LOB_TYPE_CD Impacted_LOB: Event_ID LOB_CD First user log-ins which is queried in User_Profile table. Then I have a group called CCSG Group in LOB_GRP column in Group_Lookup table. So now this CCSG Group has its respective LOB_CD in same table. Now I need to compare LOB_CD column of Group_Lookup and Impacted_LOB and display the result for that particular group. On top of this some groups like x,y,z present in LOB_GRP in Group_Lookup table should belong to CCGS Group.Can you pls help me. ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-10 : 01:44:53
|
did the previous suggestion gave you your desired output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
vignesht50
Yak Posting Veteran
82 Posts |
Posted - 2013-10-10 : 02:17:31
|
quote: Originally posted by visakh16 did the previous suggestion gave you your desired output?No Visakh Here is my original datas.Here is the one USER_PRFLUser_Id User_FullName Email User_Group BSP_LOB_Grp_LookupBSP_LOB_GRP BSP_LOB_CD BSP_LOB_GRP_TYPE_CD IMPACTED_LOBEvent_ID BSP_LOB_CD Scenario1:First user log-ins which is queried in USER_PRFL table. Then I try to relate User_Group and BSP_LOB_GRP.Now I have a value called CCSG Group in BSP_LOB_GRP column in BSP_LOB_Grp_Lookup table. So now this CCSG Group has its respective BSP_LOB_CD in same table. Now I need to compare BSP LOB_CD column of BSP_LOB_Grp_Lookup and IMPACTED_LOB and display the result for that particular group. On top of this the following values (Life, MLCT, GenAm, NEF, Annuity, Auto and Home, Annuity and Investments) present in BSP_LOB_GRP should belong to CCSG Group of the sane column.Scenario2:Assume we have two rows values like Service Leaders & Service Area in BSP_LOB_GRP. Now Service Leaders should belong to Service Area. Let’s have X&Y as the users they need to see X should be able to see LIFE, MLCT, NEF present in BSP_LOB_GRPY should be able to see Long Term Care, Total Control Account BSP_LOB_GRPThe thing is BSP_LOB_CD column in BSP_LOB_Grp_Lookup table is null.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-10 : 03:56:36
|
Scenario 1the below should give you details on CCSG GroupSELECT *FROM USER_PRFL upINNER JOIN BSP_LOB_Grp_Lookup blON bl.BSP_LOB_GRP = up.User_Group INNER JOIN IMPACTED_LOB ilON il.BSP_LOB_CD = bl.BSP_LOB_CD WHERE BSP_LOB_GRP = 'CCSG Group' but if BSP_LOB_CD column in BSP_LOB_Grp_Lookup table is null you wont get anything from above query so you need to fix that issue first------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|