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 )