Segregation of duties SIGNAL table

[santiagolc]

Hi team,

I am trying to do a segregation of duties analysis, i.e. verify that the same person is not doing certain activities, so I need to compare the creator of the purchase requisition and the creator of the purchase order item. My idea was to create a signal table where I have in principle po, po item, pr creator and oc creator. Once I have that I try to compare the code through a case when.

An image about the error:

But I am stuck when I try to solve the first point of making a table, here below I leave what I try to do:

SELECT
"PO Id"
,"PO Item"
,(SELECT "Event Created By User Id" FILTER (
    WHERE (event_name IN ('Create PO Item'))))) as "Usuario PO"
,(SELECT "Event Created By User Id" FILTER (
    WHERE (event_name IN ('Create PR Item'))))) as "Usuario PR"
FROM THIS_PROCESS
LIMIT 10<br>

An image about the error:

Thanks, best regards.

[daniel_hesseling]

Hi Santiago,

Your idea on how to approach this use case is totally right. There is just one problem with your query:
In theory there could be several times the same event_name within a case - this means: To get a single distinct value for your subquery you have to specify exactly which of these events to query. This can be done using an aggregate function such as FIRST.
The next step would then be building a subquery around the list you built to just get a final list with the POs where the user is matching. Here is an example:

It might also work using a CASE WHEN directly as you mentioned.
Best regards
Daniel

SELECT
"PO Id",
"PO Item",
"Usuario PO",
"Usuario PR"
FROM(
SELECT
"PO Id"
,"PO Item"
,(SELECT FIRST("Event Created By User Id") FILTER (
    WHERE (event_name IN ('Create PO Item')))) as "Usuario PO"
,(SELECT FIRST("Event Created By User Id") FILTER (
    WHERE (event_name IN ('Create PR Item')))) as "Usuario PR"
    FROM THIS_PROCESS
) as sub
WHERE "Usuario PO"="Usuario PR"