sqloracle-databaseselectoracle19cwith-clause

How can I assign a function result to a name in the WITH clause of a SELECT?

How can I assign a function result to a name in the WITH clause?

Tried:

with
    has_perm as ( has_perm(:user) )
select * from my_table where has_perm = 'Y'
Solution

  • Is this what you want?

    with has_perm as (
      select has_perm(:user) as has_perm
      from dual
     )
select *
from my_table
where 'Y' = (select has_perm from has_perm);

    Why not just write this without a CTE?

    select *
from my_table
where has_perm(:user) = 'Y';