Example Group Perm Link

This worksheet is an example of a complex field select that requires more than one data value from the configuration worksheet.

The GROUP_PERM_LINK database table has a column called PERMID. It has one additional field (GROUPID), but we want to focus on PERMID. The configuration data tied to the Permissions ID column is substituted for the first question mark (?) in the query. The value in the next column to the right is substituted for the second question mark (?) in the query, and the value in the next column is used for the third question mark. The screen group and application ID are strictly used to supply the information to the select statement and must be adjacent to the Permissions ID column.

Permissions ID	Screen Group	Application ID
permid,(SELECT PERMID FROM &schemaid.PERMISSIONS WHERE PERMCODE = ? AND FAMID = (select FAMID from &schemaid.PERMFAMILIES where SCREENGROUP=? and APPID=?))	screengroup	appid

Just as in the previous example, the Export Query SQL must resolve back into description text, in this case three separate values. The query is:

select p.permcode, pf.screengroup, pf.appid, gps.gpname 
from &schemaid.group_perm_lnk as gpl 
   left outer join &schemaid.groups as gps 
      on gps.groupid = gpl.groupid 
   left outer join &schemaid.permissions as p 
      on p.permid = gpl.permid 
   left outer join &schemaid.permfamilies as pf 
      on pf.famid = p.famid

The map worksheet called Group Perm Link Exp is

Permissions ID	Screen Group	Application ID
permcode	screengroup	appid