Analytics Query help

[MichaelSchmid]

Hello,

can someone help me to add a where clause for the SOD Ruleset in the following query please?
I need to update the mitigating control review, so only the risks from the Ruleset with key 14 are shown.

Thank you and BR, Michael

select ControlOwners.userkey as 'USERCONTEXT', ControlOwner as 'Country Control Owner',concat(ControlOwners.USER_GROUPDESCRIPTION," (",ControlOwners.USER_GROUPNAME,")") as 'MC Group Country', MRA.Riskname as 'Risk Name',
MRA.Description as 'Risk Desc',SR.Actualrisk as 'Risk Type',SR.username as 'Violating User',MRA.STARTDATE as 'Mitigating Control Startdate',MRA.ENDDATE as 'Mitigating Control Endate', ORG.customproperty20 as 'User Company Code'
from
(select u.username as ControlOwner,ug.USER_GROUPNAME,ug.USER_GROUPDESCRIPTION,ug.USERGROUPKEY,u.userkey
from users u, user_groups ug, usergroup_users ugu
where u.userkey = ugu.USERKEY and ugu.USER_GROUPKEY = ug.USERGROUPKEY) As ControlOwners
LEFT JOIN
(select u1.username as CFO,ug1.USER_GROUPNAME,ug1.USERGROUPKEY,u1.userkey,u1.country
from users u1, user_groups ug1, usergroup_owners ugo
where u1.userkey = ugo.USERKEY and ugo.USERGROUPKEY = ug1.USERGROUPKEY) as CFO on ControlOwners.USERGROUPKEY = CFO.USERGROUPKEY
LEFT JOIN
(select mc.MITIGATINGCONTROL,r.riskname,r.DESCRIPTION ,mra.APPROVERKEY, r.riskid,mra.STARTDATE,mra.ENDDATE,mra.ID
from mitigatingcontrols mc,mc_risk_account mra, users app, users u2, risks r
where app.userkey = mra.APPROVERKEY
and u2.userkey = mra.userkey and mra.riskid = r.RISKID
and mc.MITIGATINGCONTROLID = mra.MITIGATINGCONTROLKEY) as MRA on CFO.userkey = MRA.APPROVERKEY
LEFT JOIN
(select sr.Actualrisk,u3.userkey,u3.username,sr.MC_RISK_ACCOUNTKEY
from sodrisks sr, users u3
where sr.status=3
and u3.userkey = sr.userkey) as SR on SR.MC_RISK_ACCOUNTKEY = MRA.ID
LEFT JOIN
(select u4.userkey,u4.username,u4.customproperty20, u4.statuskey
from users u4) as ORG on ORG.userkey = SR.userkey
where (sr.Actualrisk = 'Actual Risk' or sr.Actualrisk = 'Potential Risk')
and ORG.customproperty20 = 'PIPE-AT'
and MRA.MitigatingControl = 'Inventory Postings (MB51)'
and ControlOwners.USER_GROUPNAME = 'PIPE-AT Inventory Postings (MB51)'
and ControlOwner ='atblumar'
and ORG.statuskey='1';

[naveenss · Accepted solution]

Hi @MichaelSchmid can you try the below query?

SELECT 
    ControlOwners.userkey AS 'USERCONTEXT',
    ControlOwner AS 'Country Control Owner',
    CONCAT(ControlOwners.USER_GROUPDESCRIPTION,
            ' (',
            ControlOwners.USER_GROUPNAME,
            ')') AS 'MC Group Country',
    MRA.Riskname AS 'Risk Name',
    MRA.Description AS 'Risk Desc',
    SR.Actualrisk AS 'Risk Type',
    SR.username AS 'Violating User',
    MRA.STARTDATE AS 'Mitigating Control Startdate',
    MRA.ENDDATE AS 'Mitigating Control Endate',
    ORG.customproperty20 AS 'User Company Code'
FROM
    (SELECT 
        u.username AS ControlOwner,
            ug.USER_GROUPNAME,
            ug.USER_GROUPDESCRIPTION,
            ug.USERGROUPKEY,
            u.userkey
    FROM
        users u, user_groups ug, usergroup_users ugu
    WHERE
        u.userkey = ugu.USERKEY
            AND ugu.USER_GROUPKEY = ug.USERGROUPKEY) AS ControlOwners
        LEFT JOIN
    (SELECT 
        u1.username AS CFO,
            ug1.USER_GROUPNAME,
            ug1.USERGROUPKEY,
            u1.userkey,
            u1.country
    FROM
        users u1, user_groups ug1, usergroup_owners ugo
    WHERE
        u1.userkey = ugo.USERKEY
            AND ugo.USERGROUPKEY = ug1.USERGROUPKEY) AS CFO ON ControlOwners.USERGROUPKEY = CFO.USERGROUPKEY
        LEFT JOIN
    (SELECT 
        mc.MITIGATINGCONTROL,
            r.riskname,
            r.DESCRIPTION,
            mra.APPROVERKEY,
            r.riskid,
            mra.STARTDATE,
            mra.ENDDATE,
            mra.ID
    FROM
        mitigatingcontrols mc, mc_risk_account mra, users app, users u2, risks r
    WHERE
        app.userkey = mra.APPROVERKEY
            AND u2.userkey = mra.userkey
            AND mra.riskid = r.RISKID
            AND mc.MITIGATINGCONTROLID = mra.MITIGATINGCONTROLKEY
            AND r.rulesetkey = 13) AS MRA ON CFO.userkey = MRA.APPROVERKEY
        LEFT JOIN
    (SELECT 
        sr.Actualrisk,
            u3.userkey,
            u3.username,
            sr.MC_RISK_ACCOUNTKEY
    FROM
        sodrisks sr, users u3
    WHERE
        sr.status = 3
            AND u3.userkey = sr.userkey) AS SR ON SR.MC_RISK_ACCOUNTKEY = MRA.ID
        LEFT JOIN
    (SELECT 
        u4.userkey, u4.username, u4.customproperty20, u4.statuskey
    FROM
        users u4) AS ORG ON ORG.userkey = SR.userkey
WHERE
    (sr.Actualrisk = 'Actual Risk'
        OR sr.Actualrisk = 'Potential Risk')
        AND ORG.customproperty20 = 'PIPE-AT'
        AND MRA.MitigatingControl = 'Inventory Postings (MB51)'
        AND ControlOwners.USER_GROUPNAME = 'PIPE-AT Inventory Postings (MB51)'
        AND ControlOwner = 'atblumar'
        AND ORG.statuskey = '1';