Using column generated by listagg in a where clause

Question

I have 3 tables in an oracle database. They look like this:

Table User:

username|forename|surname
a          a       a
b          b       b
c          c       c


Table Right:

username|organisationname|right
a           x              user
a           x              admin
a           x              owner
a           y              user
a           y              admin
a           z              owner
b           x              user
c           y              user
c           y              admin
c           z              user

Table Organisation:

organisationname|number
x                 12
y                 14
z                 42

The table Right has two foreign_keys: username points to the column with the same name in the table user and organisationname to the table Organisation.

For all user that have the right user on organisation x, i want to get a list of all organisations (except organisation x) where the user has 1 or more rights.

In my example: a has right user on x, b has right user on x, c has NOT the right user on x. So I just want to get all organisations (distinct!, no organisation name twice) for user a and b (and not for c!!!). If one user has no right on another organisation than x (where he must have the right user), I want that user in the output but with no organisation.

The expected output is:

user|organisations
a    y; z
b

I hope you understand what i mean. My query so far is the following:

select username, 
/* listagg put all the organisationnames of one user in one column separated by ;  
   then the organsation x gets removed from the result*/
regexp_replace (listagg (organisationname, '; ') 
       within group (order by organisationname), '(^x;?)|( ?x;)|(; x$) ', '') as "organisations"
from(
    select u2.username as username,
           o.organisationname as organisationname,
           /*I do this to remove duplicate entries in the list of organisations of one user */
           row_number() over (partition by u2.username, o.organisationname order by u2.username) as rn
       from RIGHT r2, ORGANISATION o, USER u2 
       where r2.organisationname = o.organisationname
       and r2.username = u2.username
       and r2.username IN
           (
            select u.username
            from USER u, right r
            where r.username = u.username
            and r.right = 'user'
            )  
     order by u2.username, o.organisationname         
)
where rn = 1
group by username

That query works, but now i want to filter on the column organisations. But I am not able to use it in the where clause. When I try to use it, oracle throws the error: ORA-00904: invalid identifier

Does anyone know how I can achieve this? I think oracle has problems because of the analytical function listagg. I would also be happy about suggestions how to simplify my query. Thanks!

Here is what I tried to use the column organisations in the where clause:

select * from ( ***long query shown above***) 
where organisations like '%termToSearch%';

Thorsten Kettner · Accepted Answer

Step by step: user names for user|x, then distinct user|organisation except x, then aggregation per username:

select
  username,
  listagg(organisationname, '; ') within group (order by organisationname) as organisations
from
(
  select distinct username, organisationname
  from right
  where username in 
  (
    select username 
    from right 
    where organisationname = 'x' and right = 'user'
  )
  and organisationname <> 'x'
)
group by username;

(Unfortunately LISTAGG doesn't accept a DISTINCT keyword, so we need two steps instead of one to build a list of distinct organisations.)

UPDATE: To get users without any other organisation, too, we'd remove the condition and organisationname <> 'x' and add a case construct to LISTAGG:

select
  username,
  listagg(case when organisationname <> 'x' then organisationname end, '; ')
    within group (order by organisationname) as organisations
from
(
  select distinct username, organisationname
  from right
  where username in 
  (
    select username 
    from right 
    where organisationname = 'x' and right = 'user'
  )
)
group by username;

Using column generated by listagg in a where clause

Answers (2)

Related Questions