AnsweredAssumed Answered

NSQL Help: Resource Security Access Info

Question asked by KartheepanR on Apr 13, 2010
Latest reply on Apr 14, 2010 by another_martink
Hi All,  I am trying to write an NSQL that will retrieve User Info, Primary Role, User Status, and Group Name. I have sql code that works against the database, but when I try to convert it to NSQL in clarity, I get the following error:   "NPT-0113: The dimension RESOURCE has more than one key specified."   NSQL  SELECT distinct
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:u.user_name:User_Name@,
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.first_name:First_Name@,
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.last_name:Last_Name@,
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:select unique_name from SRM_Resources where P.prprimaryroleid = id:PrimaryRoleName@,
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:case when U.user_status_id = 200 THEN 'Active' WHEN U.user_status_id = 201 THEN 'In-Active' ELSE 'Locked' END:Status@,
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:G.group_name:Group_Name@,FROM prj_resources p,
        srm_resources r,
        cmn_sec_users u,
        cmn_sec_groups_v g,
        cmn_sec_user_groups bothWHERE r.id = p.prid
          and r.user_id = u.id
          and u.id = both.user_id
          and g.id = both.group_id
          and g.language_code='en'ORDER BY u.user_name  I am fairly new to NSQL and am having a real hard time troubleshooting this error. If anyone has encountered this before, any feedback/suggestions would be most welcome.    SQL:  SELECT distinct
--P.PRID AS Project_RESOURCEID,
--R.id as ResourceIDR,
--R.User_id as "User ID",
u.user_name as "User Name",
R. First_Name as "First Name",
R.last_name as "Last Name",
--P.prprimaryroleid AS "ROLEID",
(select last_name from SRM_Resources where P.prprimaryroleid = id) "PrimaryRoleName",
case when U.user_status_id = 200 THEN 'Active' WHEN U.user_status_id = 201 THEN 'In-Active' ELSE 'Locked' END "Status",
G.group_name "Group Name"--, both.idFROM PRJ_RESOURCES P, SRM_Resources R, cmn_sec_users U,cmn_sec_groups_v G, cmn_sec_user_groups bothWHERE
R.id = P.PRID
and R.User_Id = u.id
and u.id = both.user_id
and g.id = both.group_id
and g.language_code='en'
order by u.user_name    Thank you,  Kart.  

Outcomes