I would like to use an Account group to show a list of all the users and the accounts they are the account manager of. I created this view in SQL and enabled it in Database Manager.

SELECT     COUNT(a.ACCOUNTID) AS AccountTotal, u.USERID

FROM         sysdba.USERINFO AS u

LEFT OUTER JOIN sysdba.ACCOUNT AS a ON u.USERID = a.ACCOUNTMANAGERID

GROUP BY u.USERID

I then created a new account group and I tried to create a local join between his table and the account table. I used a right join so that all of the users’ totals would show up even if they did not have any accounts. I originally tried to create it so that it would not match any records so I joined Account.AccountId to C_View.UserId. this worked in SQL but SLX did not load any records. Next I joined Account.AccountmanagerId to C_View.UserId, this also did not work. The SQL for my group looked like this

SELECT A1.ACCOUNTID, A2.USERID A2_USERID, A2.AccountTotal A2_ACCOUNTTOTAL

FROM sysdba.ACCOUNT A1 RIGHT JOIN sysdba.C_VIEW A2 ON (A1.ACCOUNTMANAGERID=A2.USERID)

ORDER BY A2.USERID ASC

My Question is 2 fold. 1) Do I need to do anything to my view other than, set the owner to sysdba, and  enable it? 2) Is there something I’m missing about the Group builder or SLX security that is preventing this from working?

I know there are other ways to show this information. I am just using this view to see if I can get it to work, the actual group I need is much more complex.

Thanks for all your help.