user restriction

[hnetscape]

Hi,

I want to get the result using a select with 2 conditions like this

if user = 'X' then

select * from abc
where

if user='Y' then

select * from abc
where

here I want to put the two conditions in a single query Is it possible, How ?. Can you please help me.

Thanks.

[julian]

Is user a column in the abc table?

[hnetscape]

Hi,

No, user is not a column in abc table. User is the current user thru which the user is connected.

thanks.

[julian]

I did the following. Is it what you are aiming at (I assume you are user called BOSS)?

create table ABC (text varchar2(10), day date);
insert into ABC values('SATDY',sysdate);
insert into ABC values('GIGIOOL',sysdate-1);
insert into ABC values('GOOOL',sysdate-2);
create user X identified by X;
create user Y identified by Y;
grant connect, resource to X;
grant connect, resource to Y;
grant select on ABC to X;
grant select on ABC to Y;

SQL> select * from boss.abc
2 where 'X' = (select username from user_users)
3 union all
4 select * from boss.abc
5 where 'Y' = (select username from user_users);

TEXT DAY
---------- ---------
SATDY 17-DEC-01
GIGIOOL 16-DEC-01
GOOOL 15-DEC-01

Elapsed: 00:00:00.20
SQL>

[jmodic]

Julian, you've lost me here. With your setup, what is your select statement trying to do?

Originally posted by julian
SQL> select * from boss.abc
2 where 'X' = (select username from user_users)
3 union all
4 select * from boss.abc
5 where 'Y' = (select username from user_users);

To reduce confusion, wouldn't it be more readable to put the above like:
SQL> select * from boss.abc
2 where 'X' = USER
3 union all
4 select * from boss.abc
5 where 'Y' = USER;

But this is not my point. What I don't understand is how does your select differ from a simple (appart for confusing the original poster ):

SQL> select * from boss.abc;

And I don't think this is what hnetscape is looking for. In fact, I think a simple use of DECODE/CASE in a WHERE clause will cover his needs, but he would have to explain in more datail what he is trying to acchieve.

[julian]

I assume that he has different where clauses on his select statements. That is how it deffers. But if he has the same where clause, then I don't see much the point of the question, it is a bit unclear to me.