Web Designer: Employees for whom a request can be placed

i'm trying to customize the web portal in a way tha allows different groups of users to place requests on behalf of defined users. The SQL is getting a bit advanced and i'm trying to figure out how to do what I want. In the code below i'm using case-when to define the groups of users with request-permissions, but the THEN-clauses are causing me trouble. Are there a better way to do this than my attempt?

-- Employees for whom a request can be placed
uid_person in ( 
select uid_person from QER_VEditEmployee where uid_personhead like
CASE 
    WHEN --ProductOwner
       (EXISTS (select 1 from personinaerole where uid_person = '%useruid%' and UID_AERole in (select uid_aerole from aerole where uid_parentaerole like 'QER-AEROLE-ITSHOPADMIN-OWNER')))
	THEN --Show everyone who can order the products owned by %useruid%
       '?'
    WHEN --ShelfOwner
       (EXISTS (select 1 from ITShopOrg where uid_personhead = '%useruid%'))
    THEN --Show everyone with access to the selves owned by %useruid%
        '?'
    WHEN --ServiceDesk (Member of permission group OrderOnBehalfExceptX)
       (EXISTS(
           select 1 from personinaerole pa
           join aerole a on pa.uid_aerole = a.uid_aerole
           join DialogGroupCollection dgc on a.uid_dialoggroup = dgc.uid_dialoggroup
           where dgc.uid_dialogGroupParent = 'CCC-B151339FAAD674499565EAC010DD1AAB'
           and pa.uid_person = '%useruid%'
           ))
    THEN -- Show everyone except X (Person.CCC_XIsInActive = '1')
       '?'
    WHEN --ServiceDesk X (Member of permission group OrderOnBehalfX)
       (EXISTS(
           select 1 from personinaerole pa
           join aerole a on pa.uid_aerole = a.uid_aerole
           join DialogGroupCollection dgc on a.uid_dialoggroup = dgc.uid_dialoggroup
           where dgc.uid_dialogGroupParent = 'CCC-04881080D4C98C48BB734763FCD4D84E'
           and pa.uid_person = '%useruid%'
           ))
    THEN -- Show X (Person.CCC_XIsInActive = '0')
       '?'           
    WHEN --ServiceDesk and ServiceDesk X (Member of permission groups OrderOnBehalfExceptX + OrderOnBehalfX)
       (EXISTS(
           select 1 from personinaerole pa
           join aerole a on pa.uid_aerole = a.uid_aerole
           join DialogGroupCollection dgc on a.uid_dialoggroup = dgc.uid_dialoggroup
           where dgc.uid_dialogGroupParent in ('CCC-04881080D4C98C48BB734763FCD4D84E','CCC-B151339FAAD674499565EAC010DD1AAB')
           and pa.uid_person = '%useruid%'
           ))
    THEN -- Show everyone
       '%'           
    ELSE --Regular manager, show everyone with QER_VEditEmployee.PersonHead = %useruid%
       '%useruid%'
END
)

  • Hi Are,

    it looks like the expression you use is a WebSQL expression that yields a string which will be treated as a SQL where clause.
    Though you have placeholders for the logged on user in that string, it's basically only *one* string.

    Maybe it would be easier to move the case distinction out of the SQL clause and handle it in the WebSQL expression instead.
    Using this approach your WebSQL expression could (for each case) deliver a different (small) SQL where claus that no longer contains case distinctions.

    Regards,

    Dennis