SQL Attribute Help

[jaisuryan]

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

[jaisuryan · Accepted solution]

Hello,

I have resolved using a bigger SQL as of now.

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')

UNION ALL

select case

when min(attrname) = 'MX_MAIL_PRIMARY' then

  (select mcmskey from idmv_entry_simple

  where mcmskeyvalue = 'ROLE3')

when min(attrname) = 'MX_MOBILE_PRIMARY' then

  (select mcmskey from idmv_entry_simple

  where mcmskeyvalue = 'ROLE4')

else null end as mskey

from idmv_value_basic t

where mskey = %USERMSKEY%

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

This might have performance issues. So if you have any help on how to improve, it would be grateful. Thanks.

Kind regards,

Jai