AnsweredAssumed Answered

Result of concatenation is too long

Question asked by elie al nahas on Jan 4, 2017
Latest reply on Jan 6, 2017 by elie al nahas



I used a sql query to concatenate some attributes. But the result is too long. I tried the query in the RSA interface and the squirrel SQL client.
I used the function listagg for concatenation.


I'm trying to create custom report. It should display the accounts with SoD violaions. It will show Full name, AD login, Supervisor, Country, Company, Region, Rule Name, Criticity (I extract it from the rule Name),
The concatenation of the application role 1 and its description, The concatenation of the application role 2 and its description, Entity 1, entity 2).
Depending on the profile code 1 and 2 that are not compatible, the application roles 1 and 2, the description of the app roles 1 and 2, the entity 1 and 2 will be
shown in the report.


An exemple about the rule name: [CRITICAL] SoD Rule - [TRAX - EXECUTION_SIGNER] [TRAX - TREASURY]
[criticity] SoD Rule - [application - Profile code1][application - Profile code2]


This is my query (I didn't put all attributes, only listagg functions):


(select distinct FIRST_NAME||', '||last_name as Full_name,RULE_NAME,listagg(case when EATTR_APPROLE_CAS1=(substr(substr(RULE_NAME,1,INSTR(RULE_NAME,'] [')-1),INSTR(RULE_NAME, '- ',1,2)+2)) then ENTITLEMENT_NAME end, ',') within group (order by ENTITLEMENT_NAME) "Applications_Roles1",listagg(case when EATTR_APPROLE_CAS1 = (substr(RULE_NAME,INSTR(RULE_NAME,'- ',1,3)+2,length(substr(RULE_NAME,INSTR(RULE_NAME,' - ',1,3)+3))-1)) then ENTITLEMENT_NAME end, ',') within group (order by ENTITLEMENT_NAME) "Applications_Roles2",
listagg(case when EATTR_APPROLE_CAS1=(substr(substr(RULE_NAME,1,INSTR(RULE_NAME,'] [')-1),INSTR(RULE_NAME, '- ',1,2)+2)) then EATTR_APPROLE_CAS4 end, ',') within group (order by EATTR_APPROLE_CAS4) "entity1",
regexp_replace(listagg(case when EATTR_APPROLE_CAS1=(substr(substr(RULE_NAME,1,INSTR(RULE_NAME,'] [')-1),INSTR(RULE_NAME, '- ',1,2)+2)) then EATTR_APPROLE_CAS9 end, ',') within group (order by EATTR_APPROLE_CAS9),'([^,]+)(,\1)*(,|$)', '\1\3') as Desc1 from V_AVR_ALL_VIOLATIONS where STATE='Open'and lower(application_name)='trax'
group by RULE_NAME, FIRST_NAME, last_name, ACCOUNT_NAME)


The concatenation is too long because some attributes like app role1, app role2, Entity 1/2 ... are long.


Moreover when i changed the application name, it worked. Maybe because attributes are short.


Could you please help me on this issue?


Best regards