title: Using column generated by listagg in a where clause
body: <p>I have 3 tables in an oracle database. They look like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The table <code>Right</code> has two foreign_keys: <code>username</code> points to the column with the same name in the table <code>user</code> and <code>organisationname</code> to the table <code>Organisation</code>.</p>
  
  <p>For all <code>user</code> that have the right user on <code>organisation</code> x, i want to get a list of all <code>organisations</code> (except organisation x) where the user has 1 or more rights.</p>
  
  <p>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.</p>
  
  <p>The expected output is:</p>
  
  <pre><code>user|organisations
  a    y; z
  b  
  </code></pre>
  
  <p>I hope you understand what i mean.
  My query so far is the following:          </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <p>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!</p>
  
  <p>Here is what I tried to use the column organisations in the where clause:</p>
  
  <pre><code>select * from ( ***long query shown above***) 
  where organisations like '%termToSearch%';  
  </code></pre>