Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    20

    Question Unanswered: Help with a SQL query (Access Matrix) with multiple outer joins

    Would need some help towards building a data access matrix (DAM) report that i'm working on.

    Here's an illustration of sample data with few rules to follow:

    • The tasks are allocated to companies.
    • The default rule is 'Available' if not suppressed. So, lookig oly at TASK table, all tasks # 1, 2, 3, 4, 5 are available to all companies.
    • The tasks can be suppressed if an entry exist in the SUPPRESS table. So, task # 1, 2, 3, 4 are supressed for the mentioned companies.
    • The tasks can be made explicitely available by adding to ACCESS table. So, task # 4 is given explicite access.
    • The SUPPRESS and ACCESS are mutual exlusive and don't have any overlaps. If such entry exist, then SUPPRESS takes precendence.
    • The companies have parent, child relationship (subsidary companies), so if a task is available in either SUPPRESS or ACCESS table, then the same is available to the child company. However, either of the roles can be overridden at child level and that takes precende.
    • For example, task # 1, 2 is suppressed for company 101, so it's suppressed for 102 & 103 as well (child / subsidary companies), but since an entry exist for 102 & 103 (child companies), so they have access.
    • As of now, the nesting in COMPANY_ASSOC table is one level, i.e. multiple company_id can have single parent_company_id (many to one relationship).


    Expected Output:
    Need all the company list where a particular task is SUPPRESSED

    Code:
    suppress_company_id (where task_id = 1)
    -------------------
    101
    Code:
    suppress_company_id (where task_id = 2)
    -------------------
    101
    102
    103
    If the task_id is supplied then it'll just return the multiple records for that task. If not, it'll just print the tasks and the company_id. The application will loop through the resultset.


    Table: TASK
    Code:
    task_id    task_type
    -------    ---------
    1          'Manager'
    2          'Worker'
    3          'Worker'
    4          'Manager'
    5          'Manager'

    Table: SUPPRESS
    Code:
    task_id    company_id    
    -------    ----------
    1          101
    2          101
    3          104
    4          105
    Table: ACCESS
    Code:
    task_id    company_id
    -------    ----------
    1          102
    1          103
    Table: COMPANY_ASSOC
    Code:
    company_id    parent_company_id    category
    ----------    -----------------    --------
    102           101                  'Premium'
    103           101                  'Premium'
    105           105                  'Premium'
    106           106                  'Gold'

    I know it sounds silly for asking help on this, but every time i run into the following error when attempt to join company_id from TASK with SUPPRESS & COMPANY_ASSOC simultaneously.
    Code:
    ORA-01468: a predicate may reference only one outer-joined table
    Thanks in advance.


    Regards,
    Rohit

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about showing the SQL you are getting this error on?
    Dave

  4. #4
    Join Date
    Nov 2011
    Posts
    20
    Quote Originally Posted by dav1mo View Post
    how about showing the SQL you are getting this error on?
    Here's the SQL i was trying so far, not sure whether I'm doing it correctly.

    Code:
      SELECT task_id, task_status
        FROM (SELECT task_id
                   , CASE task_type
                         WHEN 'Manager'
                         THEN
                             CASE access_type
                                 WHEN 'NO RS:NO RA:NO CS:NO CA' THEN 'SUPPRESS'
                                 WHEN 'NO RS:YES RA:NO CS:NO CA' THEN 'ACCESS'
                                 WHEN 'NO RS:NO RA:NO CS:YES CA' THEN 'ACCESS'
                                 WHEN 'NO RS:YES RA:YES CS:NO CA' THEN 'SUPPRESS'
                                 WHEN 'YES RS:NO RA:NO CS:YES CA' THEN 'ACCESS'
                                 WHEN 'YES RS:YES RA:NO CS:YES CA' THEN 'ACCESS'
                                 ELSE 'SUPPRESS'
                             END
                         WHEN 'Worker'
                         THEN
                             CASE access_type
                                 WHEN 'NO RS:NO RA:NO CS:NO CA' THEN 'ACCESS'
                                 WHEN 'YES RS:NO RA:NO CS:NO CA' THEN 'SUPPRESS'
                                 WHEN 'NO RS:NO RA:YES CS:NO CA' THEN 'SUPPRESS'
                                 WHEN 'YES RS:NO RA:NO CS:YES CA' THEN 'ACCESS'
                                 WHEN 'YES RS:YES RA:NO CS:YES CA' THEN 'ACCESS'
                                 WHEN 'NO RS:YES RA:NO CS:YES CA' THEN 'ACCESS'
                                 WHEN 'NO RS:YES RA:NO CS:NO CA' THEN 'ACCESS'
                                 WHEN 'NO RS:NO RA:NO CS:YES CA' THEN 'ACCESS'
                                 ELSE 'SUPPRESS'
                             END
                     END
                         task_status
                FROM (SELECT tmp.task_id
                           , tmp.task_type
                           ,    CASE NVL (r_s.id, 0)
                                    WHEN 0 THEN 'NO RS'
                                    ELSE 'YES RS'
                                END
                             || ':'
                             || CASE NVL (r_a.id, 0)
                                    WHEN 0 THEN 'NO RA'
                                    ELSE 'YES RA'
                                END
                             || ':'
                             || CASE NVL (c_s.id, 0)
                                    WHEN 0 THEN 'NO CS'
                                    ELSE 'YES CS'
                                END
                             || ':'
                             || CASE NVL (c_a.id, 0)
                                    WHEN 0 THEN 'NO CA'
                                    ELSE 'YES CA'
                                END
                                 access_type
                        FROM (SELECT task_id, task_type
                                FROM task t
                               WHERE t.task_id = 1) tmp
                           , suppress r_s
                           , accesss r_a
                           , suppress c_s
                           , accesss c_a
                        -- , company_assoc ca
                       WHERE tmp.task_id = r_s.task_id(+)
                         AND r_s.company_id(+) = 101    -- when this is joined with company_assoc, throws error
                         AND tmp.task_id = r_a.task_id(+)
                         AND r_a.company_id(+) = 101
                         AND tmp.task_id = c_s.task_id(+)
                         AND c_s.company_id(+) = 102
                         AND tmp.task_id = c_a.task_id(+)
                         AND c_a.company_id(+) = 102))
    ORDER BY task_status;

    Regards,
    Rohit
    Last edited by Rohit Oberoi; 10-08-15 at 17:15.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What is SELECT below supposed to do?
    Code:
    SELECT tmp.task_id , 
           tmp.task_type , 
           CASE Nvl (r_s.id, 0) 
                  WHEN 0 THEN 'NO RS' 
                  ELSE 'YES RS' 
           END 
                  || ':' 
                  || 
           CASE Nvl (r_a.id, 0) 
                  WHEN 0 THEN 'NO RA' 
                  ELSE 'YES RA' 
           END 
                  || ':' 
                  || 
           CASE Nvl (c_s.id, 0) 
                  WHEN 0 THEN 'NO CS' 
                  ELSE 'YES CS' 
           END 
                  || ':' 
                  || 
           CASE Nvl (c_a.id, 0) 
                  WHEN 0 THEN 'NO CA' 
                  ELSE 'YES CA' 
           END access_type 
    FROM   ( 
                  SELECT task_id, 
                         task_type 
                  FROM   TASK t 
                  WHERE  t.task_id = 1) tmp , 
           suppress r_s , 
           accesss r_a , 
           suppress c_s , 
           accesss c_a 
           -- , company_assoc ca 
    WHERE  tmp.task_id = r_s.task_id(+) 
    AND    r_s.company_id(+) = 101 -- when this is joined with company_assoc, throws error 
    AND    tmp.task_id = r_a.task_id(+) 
    AND    r_a.company_id(+) = 101 
    AND    tmp.task_id = c_s.task_id(+) 
    AND    c_s.company_id(+) = 102 
    AND    tmp.task_id = c_a.task_id(+) 
    AND    c_a.company_id(+) = 102))
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    -- , company_assoc ca
    WHERE tmp.task_id = r_s.task_id(+)
    AND r_s.company_id(+) = 101 -- when this is joined with company_assoc, throws error
    AND tmp.task_id = r_a.task_id(+)
    AND r_a.company_id(+) = 101
    AND tmp.task_id = c_s.task_id(+)
    AND c_s.company_id(+) = 102
    AND tmp.task_id = c_a.task_id(+)
    AND c_a.company_id(+) = 102))
    How and why are you trying to join to the table that is giving you the error? If you continue to not give complete info it is impossible for us to give you any kind of answer. Fault on the side of giving too much info in the future. that way we do not have to continue going back and forth. Thanks.
    Dave

Tags for this Thread

Posting Permissions

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