1 Reply Latest reply on Aug 21, 2015 12:51 PM by Gaz in Oz

    ERROR : ORA-0417:a table may be outer joined to at most one other table - BIRT Tool

    f82e1ac9-9c11-4553-92a6-ed7d16d69458

      Hey Experts,

       

      I am new to SQL Programming .

       

      I am trying to process a query in BIRT Tool, when processing am getting below mentioned error.

       

      ERROR : ORA-0417:a table may be outer joined to at most one other table

       

      1. I am trying to fetch Employee Manager first name & Last Name using self join( PA_STUDENT S ,PA_STUDENT M )

            ( i ) When i am trying to place a separator in First Name & Last Name am getting error.

                          CONCAT(S.LNAME, ' , ' ,S.FNAME) as Name_Last_First_Middle

       

      2. Using join am fetching JOB Location details (PA_JOB_LOC J )

       

      3. Now am getting error when i tried/added to pulling data from this table - PA_CPNT_EVTHST C

       

       

      SQL QUERY

       

      SELECT s.stud_id as P_Unique_Key,
             CONCAT(S.LNAME ,S.FNAME) as Name_Last_First_Middle,
             S.HIRE_DTE as Hire_Date,
             J.JL_ID,
             S.JOB_TITLE as Title,
             CONCAT(M.LNAME, M.FNAME) as MMName,
             C.CPNT_TYP_ID,
             C.CPNT_ID,
             C.CPNT_DESC,
             C.COMPL_DTE
      FROM
      PA_STUDENT S,
      PA_JOB_LOC J,
      PA_STUDENT M,
      PA_CPNT_EVTHST C
      WHERE S.JL_ID(+) = J.JL_ID
        AND S.STUD_ID(+) = C.STUD_ID
        AND S.SUPER = M.STUD_ID

       

       

      Regards,

      KR.

        • 1. Re: ERROR : ORA-0417:a table may be outer joined to at most one other table - BIRT Tool
          Gaz in Oz

          Hi K,R

           

          I've never used BIRT, so don't know what control you have over what sql it generates,... you should be able to overwrite what ever it generates though...

           

          1. For concatenating strings, instead of CONCAT(), use two vertical bars ||, so "col1||', '||coln" for example. CONCAT() only allws 2 strings to be concatenated, || allws for a lot more (data length restrictions apply, max 4000chars for varchar2 concatination).

          2. Ok.

          3. USE ANSI SQL syntax if you need to outer join to more than one table. Below is some dummy data (With clauses) and your original query reworked. Please note, I do not vouch for the joining, it is just an illustration of how to rewrite your query in a way to be able to outer join 2+ tables

           

          WITH pa_student AS(SELECT 1stud_id,
                                                       999jl_id,
                                                       'Smith' lname,
                                                       'Sarah' fname,
                                                       TRUNC(SYSDATE) - 365 hire_dte,
                                                       'IT Consultant' job_title,
                                                       111 super
                                                       FROM   dual UNION ALL
                                          SELECT 111, 102, 'Klynk', 'Coln', TRUNC(SYSDATE) - 729, 'Lecturer', 111
                                          FROM   dual),   -- s, m
                   pa_job_loc AS (SELECT 999 jl_id,
                                                       'Jakata' jl_desc
                                           FROM   dual), -- j
                   pa_cpnt_evthst AS(SELECT 10 cpnt_typ_id,
                                                             20 cpnt_id,
                                                              1 stud_id,
                                                              'foray into phorensics' cpnt_desc,
                                                              SYSDATE - 10 compl_dte
                                              FROM   dual) -- c

          SELECT s.stud_id as PK,

          s.lname||', '||s.fname as SName,
          S.HIRE_DTE as Hire_Date,
          J.JL_ID,
          S.JOB_TITLE as Title,
          m.lname||', '||m.fname as MMName,
          C.CPNT_TYP_ID,
          C.CPNT_ID,
          C.CPNT_DESC,
          C.COMPL_DTE

          FROM   pa_job_loc j

          INNER JOIN pa_student m

             ON m.stud_id = 111

          LEFT OUTER JOIN pa_student s

             ON s.jl_id = j.jl_id

          LEFT OUTER JOIN pa_cpnt_evthst c

             ON c.stud_id = s.stud_id;

           

             PK SNAME HIRE_DATE     JL_ID TITLE  MMNAMECPNT_TYP_IDCPNT_ID CPNT_DESC      COMPL_DTE

          ----- ------------ --------------- ---------- ------------- ------------ ----------- ---------- --------------------- ---------------

          1 Smith, Sarah 21-AUG-14 00:00 999 IT Consultant Klynk, Coln    10  20 foray into phorensics 11-AUG-15 22:05

           

          Cheers,

           

          Gaz.