SQL Oracle : how to find records maching a particular id in the column

Question

My query is:

select A.* 
  from (select r.role_id, 
               r.role_name, 
               r.active, 
               decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) companyName,
               LISTAGG(p.permission_id, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_id, 
               LISTAGG(p.permission_name, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_name, 
               row_number() over (order by r.created_ts desc) as RN, 
               count(*) over () as TOTAL_ROWS, 
               r.created_ts roleCreated 
          from t_role r
          left join t_role_permission rp ON r.role_id = rp.role_id
          left join t_permission p ON rp.permission_id = p.permission_id
          left join merchant m on r.entity_id = m.merchantkey 
          left join courier cour on r.entity_id = cour.courierkey 
         where 1=1
         --and p.permission_id =301446
         group by r.role_id, r.role_name, r.active, r.created_ts,
               decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) 
      ) A 
where RN between 1 and 100 order by roleCreated desc

My result is:

Now I want to filter the list on the basis of permission_id in such a way that, I want list of all the records matching that permission_id in permission_id column.

Eg: suppose I do a filter for permission_id= 301446. I want the following result

PS: adding and p.permission_id=301446 in where clause will not produce desired result.

Answer 1

For a large data set where the value of permission_id you're looking for is uncommon, querying the entire data set and then filtering on the aggregation result is going to be very inefficient, and just seems like a logically incorrect result.

What you're looking for is not the list of roles where the list of permission id's contain a particular value, but the the list of permission id's for roles that include a particular permission. Logically it works out the same in the end, but a logical implementation that more exactly matches the question being asked always seems to bring about readability and (strangely) performance benefits.

Anyway, I'm sure that you'd do better to filter to the roles that include the required permission and then run the query only for them:

 select A.* 
   from (select r.role_id, 
                r.role_name, 
                r.active, 
                decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) companyName,
                LISTAGG(p.permission_id, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_id, 
                LISTAGG(p.permission_name, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_name, 
                row_number() over (order by r.created_ts desc) as RN, 
                count(*) over () as TOTAL_ROWS, 
                r.created_ts roleCreated 
           from t_role r
           left join t_role_permission rp ON r.role_id = rp.role_id
           left join t_permission p ON rp.permission_id = p.permission_id
           left join merchant m on r.entity_id = m.merchantkey 
           left join courier cour on r.entity_id = cour.courierkey 
          where 1=1
            and r.role_id in (
                select role_id
                from   t_role_permission
                where  permission_id =301446)
          group by r.role_id, r.role_name, r.active, r.created_ts,
                decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) 
       ) A 
 where RN between 1 and 100 order by roleCreated desc

Answer 2

As an alternative to the regexp_like route, you can achieve this with analytic queries, specifically the analytic version of listagg(), and using dense_rank() to generate your limiting 'row number' value:

select distinct role_id, role_name, active, companyName, permission_id,
    permission_name, rn, total_rows, roleCreated
from (
    select *
    from (
        select r.role_id, 
            r.role_name, 
            r.active, 
            decode(r.entity_type_id, 1000, m.name, 3000, cour.name,
                4000, 'Ensenda') companyName,
            p.permission_id as raw_permission_id,
            listagg(p.permission_id, ' | ')
                within group (order by p.permission_id)
                    over (partition by r.role_id) permission_id, 
            listagg(p.permission_name, ' | ')
                within group (order by p.permission_id)
                    over (partition by r.role_id) permission_name, 
            dense_rank() over (order by r.created_ts desc) as rn,
            count(distinct r.role_id) over () as total_rows, 
            r.created_ts roleCreated
        from t_role r
        left join t_role_permission rp ON r.role_id = rp.role_id
        left join t_permission p ON rp.permission_id = p.permission_id
        left join merchant m on r.entity_id = m.merchantkey 
        left join courier cour on r.entity_id = cour.courierkey 
    )
    where raw_permission_id = 301446
)
where rn between 1 and 100
order by roleCreated desc;

If you run the inner query on its own, you'll see multiple results for each role_id; each will have the listagg components, but will also include (temporarily) the individual permission_id values, which I've aliased as raw_permission_id.

The next query out can then filter on the exact permission(s) you're interested in For a single permission, as you have here, that will give at most one row per role, but if you were looking for one of a range of permissions then you might get duplicates (if a role matched on more than one). So, the outer query excludes the raw_permission_id and adds distinct to suppress any duplicates.

Answer 3

Answer based on PasserBy comment

select A.* from(select r.role_id, r.role_name, r.active, 
decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) companyName,
LISTAGG(p.permission_id, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_id, 
LISTAGG(p.permission_name, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_name, 
row_number() over (order by r.created_ts desc) as RN, count(*) over () as TOTAL_ROWS, r.created_ts roleCreated 
from t_role r
left join t_role_permission rp ON r.role_id = rp.role_id
left join t_permission p ON rp.permission_id = p.permission_id
left join merchant m on r.entity_id = m.merchantkey 
left join courier cour on r.entity_id = cour.courierkey 
where 1=1

group by r.role_id, r.role_name, r.active, r.created_ts,
decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) 
)A where RN between 1 and 100 
and REGEXP_LIKE(a.permission_id,'(^|\s)301446(\s|$)')
order by roleCreated desc;