Table A:

Col 1. Report Name
Col 2. Report Privilege

A single report can have many privileges associated with it

Table B:

Col1. User Name
Col2. Report Privilege

A user can have a privilege which in-turn can be tied to any report in table A

Now I want to write a query which will return me an answer like Yes or No when I enter two parameters 1. User name and 2. Report Name to find out if a particular user has the access or not. I want the answer as only one row and one column i.e. Yes or No. Can it be done via straight SQL and if yes please help me out.

Thanks in advance.

dollar

It's not clear to me if user can have multiple priveleges. I will assume yes and target database is 8.1 UDB.

with allowed(flag) as
(
select 'yes'
from A, B
where A.Report = B.Report and a.User = 'blah' and b.Report = 'blah'
fetch first 1 row only
), notallowed(flag) as (values 'no')
select coalece(allowed.flag, notallowed.flag)
from notallowed left outer join allowed on 1=1

P.S.
It's easier and faster to use "1 row and yes" if access is allowed, and "no rows" if no access is allowed.

-dmitri

Thanks, it worked like a charm.

dollar

tRY the following statement:

SELECT CASE WHEN COUNT(*) > 0 THEN 'YES'
ELSE 'NO' END
FROM USER A, REPORT B
WHERE A.USERID = &USID AND B.PRIVILEG = A.PRIVILEG
AND B.REPORTID = &RPID