Request Pending For Approval || Report Query

[Manu269]

Hello Team,

I have created below runtime query to fetch the request which is pending for approval based on the request ID passed as Input.

We have a case where request is pending for approval and more than 1 approver name exist.

Assistance Required :

How can we tweak the query such that all the approver details for that request ID is displayed in a single Row?

SELECT DISTINCT SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) AS 'REQUEST ID', AR.ENDPOINTASCSV AS 'APPLICATION', ( SELECT CONCAT( FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')' ) FROM USERS WHERE AR.REQUESTOR = USERKEY ) AS 'REQUESTOR', ( SELECT CONCAT( FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')' ) FROM USERS WHERE RA.USERKEY = USERKEY ) AS 'REQUESTED FOR', AR.REQUESTDATE AS 'REQUEST DATE (UTC)' , a.username AS 'APPROVER NAME' FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA , USERS a WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY AND aa.approverkey = a.userkey AND AA.STATUS = 1 AND AR.STATUS NOT IN (4) AND SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) ='${reqID}';

[PremMahadikar]

Hi @Manu269 ,

Try Group_concat

SELECT DISTINCT
    SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', -1) AS 'REQUEST ID',
    AR.ENDPOINTASCSV AS 'APPLICATION',
    (
        SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
        FROM USERS
        WHERE AR.REQUESTOR = USERKEY
    ) AS 'REQUESTOR',
    (
        SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
        FROM USERS
        WHERE RA.USERKEY = USERKEY
    ) AS 'REQUESTED FOR',
    AR.REQUESTDATE AS 'REQUEST DATE (UTC)',
    group_concat(a.username) AS 'APPROVER NAME'
FROM ARS_REQUESTS AR,
     REQUEST_ACCESS RA,
     ACCESS_APPROVERS AA,
     USERS a
WHERE AR.REQUESTKEY = RA.REQUESTKEY
      AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
      AND aa.approverkey = a.userkey
      AND AA.STATUS = 1
      AND AR.STATUS NOT IN ( 4 )
	  GROUP by SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

[Manu269]

I tried your suggestion but the approver details are displayed twice :

[Raghu]

@Manu269  try below distinct

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', -1) AS 'REQUEST ID',
AR.ENDPOINTASCSV AS 'APPLICATION',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE AR.REQUESTOR = USERKEY
) AS 'REQUESTOR',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE RA.USERKEY = USERKEY
) AS 'REQUESTED FOR',
AR.REQUESTDATE AS 'REQUEST DATE (UTC)',
group_concat(distinct(a.username)) AS 'APPROVER NAME'
FROM ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA,
USERS a
WHERE AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND aa.approverkey = a.userkey
AND AA.STATUS = 1
AND AR.STATUS NOT IN ( 4 )
GROUP by SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) =''

[Manu269]

@Raghu already tried that option, but not sure why it is giving 2 records inspite passing 1 request ID

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', -1) AS 'REQUEST ID',
AR.ENDPOINTASCSV AS 'APPLICATION',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE AR.REQUESTOR = USERKEY
) AS 'REQUESTOR',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE RA.USERKEY = USERKEY
) AS 'REQUESTED FOR',
AR.REQUESTDATE AS 'REQUEST DATE (UTC)',
group_concat(distinct(a.username)) AS 'APPROVER NAME'
FROM ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA,
USERS a
WHERE AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND aa.approverkey = a.userkey
AND AA.STATUS = 1
AND AR.STATUS NOT IN ( 4 )
GROUP by SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) ='290072'

[Raghu · Accepted solution]

@Manu269  below without group getting data correct :

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', -1) AS 'REQUEST ID',
AR.ENDPOINTASCSV AS 'APPLICATION',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE AR.REQUESTOR = USERKEY
) AS 'REQUESTOR',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE RA.USERKEY = USERKEY
) AS 'REQUESTED FOR',
AR.REQUESTDATE AS 'REQUEST DATE (UTC)',
group_concat(distinct(a.username)) AS 'APPROVER NAME'
FROM ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA,
USERS a
WHERE AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND aa.approverkey = a.userkey
AND AA.STATUS = 1
AND AR.STATUS NOT IN ( 4 )
and SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) =9830188