Results 1 to 4 of 4

Thread: SQL Statement

  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Arrow Unanswered: SQL Statement

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: SQL Statement

    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

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: SQL Statement

    Thanks, it worked like a charm.

    dollar

    Originally posted by chuzhoi
    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

  4. #4
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •