Many times we get business requirements to generate a customized report of all users with several attributes like MSKEYVALUE, DISPLAYNAME, MAIL, MOBILE, COUNTRY etc.
If we want to keep all attributes of each user in a single row then it generally requires running a multiple sql-queries and then combining them together in a single file. It is time-consuming and manual work involved may lead to error.
Following single query can be used to generate such custom reports.
Select distinct
(select mskey from idmv_vallink_basic with (nolock) where mcAttrName='MSKEYVALUE' and MSKEY = M.mskey) as MSKEY,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MSKEYVALUE' and MSKEY = M.mskey) as MSKEYVALUE,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='DISPLAYNAME' and MSKEY = M.mskey) as DISPLAYNAME,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_MAIL_PRIMARY' and MSKEY = M.mskey) as MAIL,
(select mcValue from idmv_vallink_basic with (nolock) where mcAttrName ='MX_ADDRESS_COUNTRY' and MSKEY = M.mskey) as COUNTRY,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_PHONE_PRIMARY' and MSKEY = M.mskey) as Phone,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_MOBILE_PRIMARY' and MSKEY = M.mskey) as Mobile,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_VALIDFROM' and MSKEY = M.mskey) as ValidFrom,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_VALIDTO' and MSKEY = M.mskey) as ValidTo,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_DISABLED' and MSKEY = M.mskey) as Disabled_status
from idmv_vallink_basic M with (nolock) where M.mcAttrName='MX_ENTRYTYPE' and M.mcValue='MX_PERSON'
Above query will result, all users (MX_ENTRYTYPE = MX_PERSON) with their Mskey and value of attributes MSKEYVALUE, DISPLAYNAME, MX_MAIL_PRIMARY, MX_ADDRESS_COUNTRY, MX_PHONE_PRIMARY, MX_MOBILE_PRIMARY, MX_VALIDFROM, MX_VALIDTO and MX_DISABLED.
If a user will not have any of the above attributes then query will result NULL for that attribute.
The result will look like
Based on requirement, Attributes can be removed/added in the above query.
The above query can be modified to generate even more customized report.
Example - To generate of users (MX_ENTRYTYPE = MX_PERSON) with their Mskey and value of attributes MSKEYVALUE, DISPLAYNAME, MX_MAIL_PRIMARY, MX_ADDRESS_COUNTRY, MX_PHONE_PRIMARY, MX_MOBILE_PRIMARY, MX_VALIDFROM, MX_VALIDTO and MX_DISABLED whose MSKEYVALUE starts from TEST.
Query can be written as
Select distinct
(select mskey from idmv_vallink_basic with (nolock) where mcAttrName='MSKEYVALUE' and MSKEY = M.mskey) as MSKEY,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MSKEYVALUE' and MSKEY = M.mskey) as MSKEYVALUE,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='DISPLAYNAME' and MSKEY = M.mskey) as DISPLAYNAME,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_MAIL_PRIMARY' and MSKEY = M.mskey) as MAIL,
(select mcValue from idmv_vallink_basic with (nolock) where mcAttrName ='MX_ADDRESS_COUNTRY' and MSKEY = M.mskey) as COUNTRY,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_PHONE_PRIMARY' and MSKEY = M.mskey) as Phone,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_MOBILE_PRIMARY' and MSKEY = M.mskey) as Mobile,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_VALIDFROM' and MSKEY = M.mskey) as ValidFrom,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_VALIDTO' and MSKEY = M.mskey) as ValidTo,
(select mcvalue from idmv_vallink_basic with (nolock) where mcAttrName='MX_DISABLED' and MSKEY = M.mskey) as Disabled_status
from idmv_vallink_basic M with (nolock), idmv_vallink_basic N with (nolock) where M.mcAttrName='MX_ENTRYTYPE' and
M.mcValue='MX_PERSON' and N.mcAttrName='MSKEYVALUE' and N.mcvalue like 'TEST%' and M.mskey=N.mskey
The result will look like
P.S. - Please note that above query will work perfectly with single value attributes while for multivalue attribute it will return only one value which will be picked randomly therefore use this query wisely for multi-value attribute. with (nolock) is specific to MS SQL so if you want to run this query on Oracle/DB2 don't forget to remove with (nolock).
Hope It will help to prepare reports :smile:
C Kumar
- SAP Managed Tags:
17 Comments
