how to write a Oracle view with this query?

Question

I am using oracle 11g. I want to query with 3 queries.

First one, it is the main. Select to find condition.

  Select role, name from tablerole where name is not null and ID=@param1;

This query will return @role with 2 level (admin and user)

The 2 other sub queries will base on this condition.

Up on the first query.

 If role='admin' then select*from tablescreen where name is not null and ID=@param1;

 If role='user' then select*from tablescreen where name='2'; 

 @param1 is given when I call this view or when I using.

I consider it will be view or function or procedure best for this query.

How to write this query that can accept an input parameter into the query for looping source and return output where matched condition.

Thanks for your kindness.

Nam hmoob.

Answer 1

As far as I understood the question, that would be a view that consists of two select statements:

• one that uses condition for admin
• another one for user

The first query you posted will be used in a join with the tablescreen table. Something like this:

create or replace view v_role as
-- select for ADMIN
select r.id, s.whatever
  from tablescreen s join tablerole r on r.id = s.id
  where s.name is not null
    and r.role = 'admin'
union all
-- select for USER
select r.id, s.whatever
  from tablescreen s join tablerole r on r.id = s.id
  where s.name = '2'
    and r.name = 'user';

Or, as Zynon suggested, without union:

select r.id, s.whatever
  from tablescreen s join tablerole r on r.id = s.id
  where (    s.name is not null
         and r.role = 'admin'
        )
     or (    s.name = '2'
         and r.name = 'user'
        );

You'd then use it as

select * from v_role v where v.id = &param1;