Skip to Content
1
Apr 07, 2015 at 04:39 PM

SQL Attribute Help

23 Views

Hi Experts,

I need your help on one of my customization.

Previous requirement: If user has MX_MAIL_PRIMARY set, then display role1 in available roles. If MX_MOBILE_PRIMARY is set, display role2

We achieved this using a "SELECT CASE" statement as below in attribute values table of MXREF_MX_ROLE in UI task.

select case

when min(attrname) = 'MX_MAIL_PRIMARY' then

(select mcmskey from idmv_entry_simple

where mcmskeyvalue = 'ROLE1')

when min(attrname) = 'MX_MOBILE_PRIMARY' then

(select mcmskey from idmv_entry_simple

where mcmskeyvalue = 'ROLE2')

else null end as mskey

from idmv_value_basic t

where mskey = %USERMSKEY%

and attrname in ('MX_MAIL_PRIMARY','MX_MOBILE_PRIMARY')

New requirement: If user has MX_MAIL_PRIMARY set, then display role1, role3. If MX_MOBILE_PRIMARY is set, display role2, role3

I tried below two SQLs but no success, I get error message as "Could not search for entries" in UI

select case

when min(attrname) = 'MX_MAIL_PRIMARY' then

(select mcmskey from idmv_entry_simple

where mcmskeyvalue in ('ROLE1','ROLE3'))

when min(attrname) = 'MX_MOBILE_PRIMARY' then

(select mcmskey from idmv_entry_simple

where mcmskeyvalue in ('ROLE2','ROLE3'))

else null end as mskey

from idmv_value_basic t

where mskey = %USERMSKEY%

and attrname in ('MX_MAIL_PRIMARY','MX_MOBILE_PRIMARY')

Above SQL throws error if I directly execute it in SQL editor as "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

if ((select min(attrname) from idmv_value_basic where mskey = %USERMSKEY% and attrname in ('MX_MAIL_PRIMARY','MX_MOBILE_PRIMARY')) = 'MX_MAIL_PRIMARY')

select mcmskey from idmv_entry_simple where mcmskeyvalue in ('ROLE1','ROLE3')

Else if ((select min(attrname) from idmv_value_basic t where mskey = %USERMSKEY% and attrname in ('MX_MAIL_PRIMARY','MX_MOBILE_PRIMARY')) = 'MX_MOBILE_PRIMARY')

select mcmskey from idmv_entry_simple where mcmskeyvalue in ('ROLE2','ROLE3')

Above SQL executes like charm in SQL editor but when used in attribute value, no success ๐Ÿ˜”

Have anyone else had this requirement to display roles based on condition? if so, how you implemented?

Anyone can confirm if we can use "IF.. ELSE" in Attribute Value query?

Kind regards,

Jaisuryan