When I login as admin, I was able to view the data on the form view. As I regular user, some of the fields doesn't display data.  Als when run the SQL profiler for the same form view functionality between Admin and regular user, Admin works fine with no errors. But regular user throws join error

2013/03/25 14:42:17.441               2147755903         4468         1.  0.  0.127        Join type not supported (not INNER join is used). (SQL: "SELECT A1.C_ORGANIZATIONID, A1.ORGANIZATION_NR, A1.NAME, A2.ACCOUNT A2_ACCOUNT, A2.C_NON_PARTICIPANT_ORG_NR A2_C_NON_PARTICIPANT_ORG_NR, A1.TYPE_ITEMID, A1.SUB_TYPE_ITEMID, A3.ACCOUNT A3_ACCOUNT, A4.NAME A4_NAME, A1.AUM_AMT, A5.TEXT A5_TEXT, A1.AT_RISK_FLAG, A1.AT_RISK_DT, A1.PRIMARY_CBIS_REP_USERID, A6.ADDRESSES A6_ADDRESSES, A6.CITY A6_CITY, A6.STATE A6_STATE, A6.COUNTRY A6_COUNTRY, A1.OPENED_DT, A1.RESOLUTION_DT, A1.CLOSED_DT FROM C_ORGANIZATION A1 LEFT OUTER JOIN ADDRESS A6 ON (A1.PRIMARY_ADDRESSID=A6.ADDRESSID) LEFT OUTER JOIN PICKLIST A5 ON (A1.CURRENT_STATUS_ITEMID=A5.ITEMID) LEFT OUTER JOIN C_RELIGIOUS_ORDER A4 ON (A1.C_RELIGIOUS_ORDERID=A4.C_RELIGIOUS_ORDERID) LEFT OUTER JOIN ACCOUNT A3 ON (A1.DIOCESE_ACCOUNTID=A3.ACCOUNTID) LEFT OUTER JOIN ACCOUNT A2 ON (A1.MASTER_REC_ACCOUNTID=A2.ACCOUNTID) ORDER BY A1.ORGANIZATION_NR ASC   ")

Please let me know

Thanks

This is the output of what SLX actually used. Can you supply the upper area (this is what you tried to send in the query to SLX Parser). Note: not all join types are supported and not all SQL (for example, UPDATE FROM is not valid when using SLX Provider)

Also, as an aside. The Admin user is unrestricted - whereas a regular user will have a security layer added automatically. So, a simple SELECT COL FROM TABLE will flow as exactly that for Admin. When you do the same as a user then automatic joins are added to security tables to ensure the user can only see the data applicable to them. This is automatic and non-escapable. Hence, you should only ever test the system as a user, not admin. Admin is used for administrative tasks and is a free license - it's not to be used for everyday access.

You mean this:

---------- Client SQL ---------
SELECT A1.C_ORGANIZATIONID, A1.ORGANIZATION_NR, A1.NAME, A2.ACCOUNT A2_ACCOUNT, A2.C_NON_PARTICIPANT_ORG_NR A2_C_NON_PARTICIPANT_ORG_NR, A1.TYPE_ITEMID, A1.SUB_TYPE_ITEMID, A3.ACCOUNT A3_ACCOUNT, A4.NAME A4_NAME, A1.AUM_AMT, A5.TEXT A5_TEXT, A1.AT_RISK_FLAG, A1.AT_RISK_DT, A1.PRIMARY_CBIS_REP_USERID, A6.ADDRESSES A6_ADDRESSES, A6.CITY A6_CITY, A6.STATE A6_STATE, A6.COUNTRY A6_COUNTRY, A1.OPENED_DT, A1.RESOLUTION_DT, A1.CLOSED_DT FROM C_ORGANIZATION A1 LEFT OUTER JOIN ADDRESS A6 ON (A1.PRIMARY_ADDRESSID=A6.ADDRESSID) LEFT OUTER JOIN PICKLIST A5 ON (A1.CURRENT_STATUS_ITEMID=A5.ITEMID) LEFT OUTER JOIN C_RELIGIOUS_ORDER A4 ON (A1.C_RELIGIOUS_ORDERID=A4.C_RELIGIOUS_ORDERID) LEFT OUTER JOIN ACCOUNT A3 ON (A1.DIOCESE_ACCOUNTID=A3.ACCOUNTID) LEFT OUTER JOIN ACCOUNT A2 ON (A1.MASTER_REC_ACCOUNTID=A2.ACCOUNTID) ORDER BY A1.ORGANIZATION_NR ASC  
---------- Executed SQL ----------
SELECT A1.C_ORGANIZATIONID, A1.ORGANIZATION_NR, A1.NAME, A2.ACCOUNT A2_ACCOUNT, A2.C_NON_PARTICIPANT_ORG_NR A2_C_NON_PARTICIPANT_ORG_NR, A1.TYPE_ITEMID, A1.SUB_TYPE_ITEMID, A3.ACCOUNT A3_ACCOUNT, A4.NAME A4_NAME, A1.AUM_AMT, A5.TEXT A5_TEXT, A1.AT_RISK_FLAG, A1.AT_RISK_DT, A1.PRIMARY_CBIS_REP_USERID, (isNull(A6.ADDRESS1,'') + ' ' + isNull(A6.ADDRESS2,'')) A6_ADDRESSES, A6.CITY A6_CITY, A6.STATE A6_STATE, A6.COUNTRY A6_COUNTRY, A1.OPENED_DT, A1.RESOLUTION_DT, A1.CLOSED_DT,A2.SECCODEID SLXSECCODEID22,A3.SECCODEID SLXSECCODEID23 FROM C_ORGANIZATION A1 LEFT OUTER JOIN ADDRESS A6 ON (A1.PRIMARY_ADDRESSID=A6.ADDRESSID) LEFT OUTER JOIN PICKLIST A5 ON (A1.CURRENT_STATUS_ITEMID=A5.ITEMID) LEFT OUTER JOIN C_RELIGIOUS_ORDER A4 ON (A1.C_RELIGIOUS_ORDERID=A4.C_RELIGIOUS_ORDERID) LEFT OUTER JOIN ACCOUNT_ A3 ON (A1.DIOCESE_ACCOUNTID=A3.ACCOUNTID) AND A3.ACCESSID_='UQF8AA00000N'  LEFT OUTER JOIN ACCOUNT_ A2 ON (A1.MASTER_REC_ACCOUNTID=A2.ACCOUNTID) AND A2.ACCESSID_='UQF8AA00000N'   ORDER BY A1.ORGANIZATION_NR ASC

OR

 Time Stamp Error Code rocess ID Machine Error Message
2013/03/25 15:52:59.082 2147755903 6076   1.  0.  0.127 Join type not supported (not INNER join is used). (SQL: "SELECT A1.C_ORGANIZATIONID, A1.ORGANIZATION_NR, A1.NAME, A2.ACCOUNT A2_ACCOUNT, A2.C_NON_PARTICIPANT_ORG_NR A2_C_NON_PARTICIPANT_ORG_NR, A1.TYPE_ITEMID, A1.SUB_TYPE_ITEMID, A3.ACCOUNT A3_ACCOUNT, A4.NAME A4_NAME, A1.AUM_AMT, A5.TEXT A5_TEXT, A1.AT_RISK_FLAG, A1.AT_RISK_DT, A1.PRIMARY_CBIS_REP_USERID, A6.ADDRESSES A6_ADDRESSES, A6.CITY A6_CITY, A6.STATE A6_STATE, A6.COUNTRY A6_COUNTRY, A1.OPENED_DT, A1.RESOLUTION_DT, A1.CLOSED_DT FROM C_ORGANIZATION A1 LEFT OUTER JOIN ADDRESS A6 ON (A1.PRIMARY_ADDRESSID=A6.ADDRESSID) LEFT OUTER JOIN PICKLIST A5 ON (A1.CURRENT_STATUS_ITEMID=A5.ITEMID) LEFT OUTER JOIN C_RELIGIOUS_ORDER A4 ON (A1.C_RELIGIOUS_ORDERID=A4.C_RELIGIOUS_ORDERID) LEFT OUTER JOIN ACCOUNT A3 ON (A1.DIOCESE_ACCOUNTID=A3.ACCOUNTID) LEFT OUTER JOIN ACCOUNT A2 ON (A1.MASTER_REC_ACCOUNTID=A2.ACCOUNTID) ORDER BY A1.ORGANIZATION_NR ASC   ")

Yes, from a quick glance you have some questionable activty around the address table. You appear to be linking from a new ID (primary_addressid). I would have expected account.addressid to address.addressid.

Also, you appear to be storing an ID in current_status_itemid back to picklist (not sure why? Why don't you store just the data, rather than the ID to the data? And, if it's anything to do with 3NF - put that out of your mind straight away! Waste of time - considering it's not used much elsewhere!