1 2 Previous Next 26 Replies Latest reply: Jun 19, 2013 11:00 PM by Mahir M. Quluzade RSS

    2 Left outer join error

    yxes2013

      Hi all,

       

      11.2.0.1

       

      I have two (2) left outer join in my query , but I got errror

      Is ths supported?

       

      It someting like :

       

      select a.col1, b.col1, c.col1 from tab1 a

      LEFT OUTER JOIN tab2 b

      ON a.id = b.id,

      LEFT OUTER JOIN tab3 c

      ON a.id = c.id;

       

      Is my syntax correct? I got ora-942 error.

       

      Please help...

       

      Thanks

        • 1. Re: 2 Left outer join error
          Pacmann

          Hi,

           

          Remove comma :

           

          select a.col1, b.col1, c.col1 from tab1 a

          LEFT OUTER JOIN tab2 b

          ON a.id = b.id

          LEFT OUTER JOIN tab3 c

          ON a.id = c.id;

          • 2. Re: 2 Left outer join error
            BCV

            Hi,

             

              Syntax Error,remove the Comma,Try like this,

             

            SELECT a.Col1, b.Col1, c.Col1

              FROM tab1 a LEFT OUTER JOIN tab2 b ON a.ID = b.ID

                   LEFT OUTER JOIN tab3 c ON a.ID = c.ID;

            • 3. Re: 2 Left outer join error
              yxes2013

              I already tried that but same error

              • 4. Re: 2 Left outer join error
                BCV

                Can u Post the error here...?

                • 5. Re: 2 Left outer join error
                  Peter vd Zwan

                  Please look at the error description.

                  ORA-00942: table or view does not exist

                   

                  This error has nothing to do with the left join!

                   

                  Regards,

                   

                  Peter

                  • 6. Re: 2 Left outer join error
                    Sandeep M.

                    Syntax itself is wrong, use this instead

                     

                    select a.col1, b.col1, c.col1 from tab1 a

                    LEFT OUTER JOIN tab2 b LEFT OUTER JOIN tab3 c

                    ON a.id = b.id

                    AND a.id = c.id;

                    • 7. Re: 2 Left outer join error
                      yxes2013

                      Hi all,

                       

                      This is the actual code:

                       

                      [code]

                      CREATE MATERIALIZED VIEW ACR_MASTER_MVW2 ("ACR_NO", "LASTNAME", "FIRSTNAME", "MIDDLENAME", "BIRTHDATE","GENDER", "NATIONALITY", "PROB_STAY_LENGHT", "SECTION_ISSUED", "RESIDENCE_CERTIFICATE_NO", "STATUS", "PHOTO", "SIGNATURE", "CARD_NO","CARD_VALIDITY", "CARD_SERIAL_NO", "CARD_DATE_ISSUED", "CARD_STATUS")

                      AS SELECT A.ACRNUMBER AS ACR_NO, A.LASTNAME, A.GIVENNAME AS FIRSTNAME, A.MIDDLENAME, A.BIRTHDATE, A.GENDER,NLV2(F.COUNTRYID3,'XXX'), A.PROBSTAYLENGTH AS PROB_STAY_LENGHT,A.SECTIONISSUED AS SECTION_ISSUED, A.RESIDENCECERTIFICATENUMBER AS RESIDENCE_CERTIFICATE_NO, A.ACTIVESTATUS AS STATUS, B.CLIPIMAGE AS PHOTO, C.SIGBMP AS SIGNATURE,D.CARDISSUErNUMBER AS CARD_NO, D.CARDEXPIRYDATE AS CARD_VALIDITY, D.CARDSERIALNUMBER AS CARD_SERIAL_NO, D.CARDISSUEDATE AS CARD_DATE_ISSUED, nvl2( d.CARDSERIALNUMBER,'0','1') AS CARD_STATUS

                      FROM

                      ACRMASTER      A,

                      PICTURES       B,

                      SIGNATURE      C,

                      ACRCARDDETaILS D,

                      left outer join ACRBLOCKCARDDETAILS E on(d.acrnumber=e.acrnumber and d.CARDSERIALNUMBER=e.CARDSERIALNUMBER)

                      left outer join COUNTRYMASTER_LK    F on(A.NATIONALITY=F.ID)

                      WHERE

                      a.acrnumber=b.acrnumber and

                      b.acrnumber=c.acrnumber and

                      c.acrnumber=d.acrnumber ;

                      [/code]

                       

                      [code]

                      SQL> CREATE MATERIALIZED VIEW ACR_MASTER_MVW2 ("ACR_NO", "LASTNAME", "FIRSTNAME", "MIDDLENAME", "BIRTHDATE","GENDER", "NATIONALITY", "PROB_STAY_LENGHT", "SECTION_ISSUED", "RESIDENCE_CERTIFICATE_NO", "STATUS", "PHOTO", "SIGNATURE", "CARD_NO","CARD_VALIDITY", "CARD_SERIAL_NO", "CARD_DATE_ISSUED", "CAR

                      D_STATUS")

                        2  AS SELECT A.ACRNUMBER AS ACR_NO, A.LASTNAME, A.GIVENNAME AS FIRSTNAME, A.MIDDLENAME, A.BIRTHDATE, A.GENDER,NLV2(F.COUNTRYID3,'XXX'), A.PROBSTAYLENGTH AS PROB_STAY_LENGHT,A.SECTIONISSUED AS SECTION_ISSUED, A.RESIDENCECERTIFICATENUMBER AS RESIDENCE_CERTIFICATE_NO, A.ACTIVESTATUS AS STATUS, B.CLIP

                      IMAGE AS PHOTO, C.SIGBMP AS SIGNATURE,D.CARDISSUErNUMBER AS CARD_NO, D.CARDEXPIRYDATE AS CARD_VALIDITY, D.CARDSERIALNUMBER AS CARD_SERIAL_NO, D.CARDISSUEDATE AS CARD_DATE_ISSUED, nvl2( d.CARDSERIALNUMBER,'0','1') AS CARD_STATUS

                        3  FROM

                        4  ACRMASTER      A,

                        5  PICTURES       B,

                        6  SIGNATURE      C,

                        7  ACRCARDDETaILS D,

                        8  left outer join ACRBLOCKCARDDETAILS E on(d.acrnumber=e.acrnumber and d.CARDSERIALNUMBER=e.CARDSERIALNUMBER)

                        9  left outer join COUNTRYMASTER_LK    F on(A.NATIONALITY=F.ID)

                      10  WHERE

                      11  a.acrnumber=b.acrnumber and

                      12  b.acrnumber=c.acrnumber and

                      13  c.acrnumber=d.acrnumber ;

                      left outer join ACRBLOCKCARDDETAILS E on(d.acrnumber=e.acrnumber and d.CARDSERIALNUMBER=e.CARDSERIALNUMBER)

                      *

                      ERROR at line 8:

                      ORA-00942: table or view does not exist

                      [/code]

                      • 8. Re: 2 Left outer join error
                        yxes2013

                        Hi all,

                         

                        The 2 left joined tables are just small look up tables (like county code ('USA') and blacklist code . Less than 1000 rows each.

                        • 9. Re: 2 Left outer join error
                          BCV

                          Make Sure That all Tables  You Used in this View are Exists are Not...

                          If Sysnonyms and Grants  are not there for your tables means .. just give it... Suerly your Query Will Work.

                           

                          Cheers...

                          • 10. Re: 2 Left outer join error
                            Mahir M. Quluzade

                            You have error on table does not exisits.

                            Check table names. May be have errors on table names or you can not select privelege on this table.

                             

                            You  can  check table names from ALL_TABLES.

                             

                            select *  from all_tables where table_name = 'ACRMASTER';

                            select *  from all_tables where table_name = 'PICTURES';

                            select *  from all_tables where table_name = 'SIGNATURE';

                            select *  from all_tables where table_name = 'ACRCARDDETaILS';

                            select *  from all_tables where table_name = 'ACRBLOCKCARDDETAILS';

                            select *  from all_tables where table_name = 'COUNTRYMASTER_LK';

                             

                             

                            Regards

                            Mahir M. Quluzade

                            • 11. Re: 2 Left outer join error
                              34MCA2K2

                              This error is self explanatory in most of the cases. FTF - Are you able to execute the select statement on table ACRBLOCKCARDDETAILS?

                              • 12. Re: 2 Left outer join error
                                yxes2013

                                Hi Sandeep,

                                 

                                I got different error if I follow your syntax

                                 

                                [code]

                                SQL>

                                SQL> CREATE MATERIALIZED VIEW ACR_MASTER_MVW2 ("ACR_NO", "LASTNAME", "FIRSTNAME", "MIDDLENAME", "BIRTHDATE","GENDER", "NATIONALITY", "PROB_STAY_LENGHT", "SECTION_ISSUED", "RESIDENCE_CERTIFICATE_NO", "STATUS", "PHOTO", "SIGNATURE", "CARD_NO","CARD_VALIDITY", "CARD_SERIAL_NO", "CARD_DATE_ISSUED", "CAR

                                D_STATUS")

                                  2  AS SELECT A.ACRNUMBER AS ACR_NO, A.LASTNAME, A.GIVENNAME AS FIRSTNAME, A.MIDDLENAME, A.BIRTHDATE, A.GENDER,NLV2(F.COUNTRYID3,'XXX'), A.PROBSTAYLENGTH AS PROB_STAY_LENGHT,A.SECTIONISSUED AS SECTION_ISSUED, A.RESIDENCECERTIFICATENUMBER AS RESIDENCE_CERTIFICATE_NO, A.ACTIVESTATUS AS STATUS, B.CLIP

                                IMAGE AS PHOTO, C.SIGBMP AS SIGNATURE,D.CARDISSUErNUMBER AS CARD_NO, D.CARDEXPIRYDATE AS CARD_VALIDITY, D.CARDSERIALNUMBER AS CARD_SERIAL_NO, D.CARDISSUEDATE AS CARD_DATE_ISSUED, nvl2( d.CARDSERIALNUMBER,'0','1') AS CARD_STATUS

                                  3  FROM

                                  4  ACRMASTER      A,

                                  5  PICTURES       B,

                                  6  SIGNATURE      C,

                                  7  ACRCARDDETaILS D,

                                  8  left outer join ACRBLOCKCARDDETAILS E

                                  9  left outer join COUNTRYMASTER_LK F

                                10  on

                                11  d.acrnumber=e.acrnumber and d.CARDSERIALNUMBER=e.CARDSERIALNUMBER and A.NATIONALITY=F.ID

                                12  WHERE

                                13  a.acrnumber=b.acrnumber and

                                14  b.acrnumber=c.acrnumber and

                                15  c.acrnumber=d.acrnumber ;

                                WHERE

                                *

                                ERROR at line 12:

                                ORA-00905: missing keyword

                                [/code]

                                 

                                 

                                Can you tell me please how to used fomatting here [code] and [/code]

                                I does not display my print format

                                 

                                Thanks

                                • 13. Re: 2 Left outer join error
                                  Greg Spall

                                  Please show the results of this query:

                                   

                                  select *
                                    from user_tab_privs
                                   where table_name in (
                                                    'ACRMASTER', 'PICTURES', 'SIGNATURE',
                                                    'ACRCARDDETAILS', 'ACRBLOCKCARDDETAILS', 'COUNTRYMASTER_LK'
                                                   );
                                  
                                  • 14. Re: 2 Left outer join error
                                    yxes2013

                                    Hi Mahir,

                                     

                                    SQL> select table_name  from all_tables where table_name = 'ACRMASTER';

                                     

                                     

                                    TABLE_NAME

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

                                    ACRMASTER

                                     

                                     

                                    SQL> select table_name  from all_tables where table_name = 'PICTURES';

                                     

                                     

                                    TABLE_NAME

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

                                    PICTURES

                                     

                                     

                                    SQL> select table_name  from all_tables where table_name = 'SIGNATURE';

                                     

                                     

                                    TABLE_NAME

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

                                    SIGNATURE

                                     

                                     

                                    SQL> select table_name  from all_tables where table_name = 'ACRCARDDETAILS';

                                     

                                     

                                    TABLE_NAME

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

                                    ACRCARDDETAILS

                                     

                                     

                                    SQL> select table_name  from all_tables where table_name = 'ACRBLOCKCARDDETAILS';

                                     

                                     

                                    TABLE_NAME

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

                                    ACRBLOCKCARDDETAILS

                                     

                                     

                                    SQL> select table_name  from all_tables where table_name = 'COUNTRYMASTER_LK';

                                     

                                     

                                    TABLE_NAME

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

                                    COUNTRYMASTER_LK

                                    1 2 Previous Next