This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jun 19, 2013 9:00 PM by MahirM.Quluzade RSS

2 Left outer join error

yxes2013 Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    I already tried that but same error

  • 4. Re: 2 Left outer join error
    BCV Journeyer
    Currently Being Moderated

    Can u Post the error here...?

  • 5. Re: 2 Left outer join error
    Peter vd Zwan Expert
    Currently Being Moderated

    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
    SandeepM. Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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
    MahirM.Quluzade Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points