1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ora 000904 error on join query

Discussion in 'SQL PL/SQL' started by vikumar, Sep 21, 2010.

  1. vikumar

    vikumar Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    select e.emp_no,e.f_name,b.brnch_name from emp_mstrv e,brnch_mstrv b
    where b.brnch_no = e.brnch_no;

    where b.brnch_no = e.brnch_no
    *
    ERROR at line 2:
    ORA-00904: "E"."BRNCH_NO": invalid identifier
    table schema given

    SQL> desc emp_mstrv
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    emp_no NOT NULL VARCHAR2(5)
    f_name VARCHAR2(10)
    l_name VARCHAR2(10)
    dept VARCHAR2(10)
    desg VARCHAR2(5)
    brnch_no VARCHAR2(5)

    SQL> desc brnch_mstrv
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    brnch_no NOT NULL VARCHAR2(5)
    brnch_name VARCHAR2(10)


    i could not get rid of error..
    please suggest something.
    thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your create table statement enclosed the column names in full quotes (") therefore you must use full quotes to reference them. Try this:

    Code (SQL):
     
    SELECT e."emp_no",e."f_name",b."brnch_name" FROM emp_mstrv e,brnch_mstrv b
    WHERE b."brnch_no" = e."brnch_no";
     
    Had you not used full quotes around the column names using desc on the tables would have returned column names in all capital letters.
     
    nestor likes this.
  3. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    That was a great observation zargon... that's pretty much correct.
     
    vikumar likes this.
  4. vikumar

    vikumar Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    first of all thanks for ur attention towards this...

    i tried the same,but the result is again,an error........

    SQL> SELECT e."emp_no",e."f_name",b."brnch_name" FROM emp_mstrv e,brnch_mstrv b
    2 WHERE b."brnch_no" = e."brnch_no";
    WHERE b."brnch_no" = e."brnch_no"
    *
    ERROR at line 2:
    ORA-00904: "E"."brnch_no": invalid identifier

    i m using oracle 9i release 9.2.0.1.0

    the error,it shows is always after the '=' sign in the 'where' clause.

    please suggest,
     
  5. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    can u attach the create table script of both tables so that we can further examine the table structure.
     
  6. vikumar

    vikumar Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    here are the script.......

    SQL> create table brnch_mstrv("brnch_no" varchar(5) primary key,"brnch_name" varchar2(10));

    SQL> create table emp_mstrv("emp_no" varchar(5) primary key,"f_name" varchar2(10),
    "l_name" varchar2(10),"dept" varchar2(10),"desg" varchar(5),"brnch_no" varchar(5));
     
  7. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    one more thing, are you the owner of the two tables? If not do you have privilege to select from them, i'm not sure if it can be the cause but you can try.
     
  8. vikumar

    vikumar Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    ya ... i have the priviledge..



    SQL> select * from brnch_mstrv;

    brnch brnch_name
    ----- ----------
    b001 alpha
    b002 beta
    b003 gamma
    b005 delta
    b006 theta
     
  9. vikumar

    vikumar Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    ya ... i have the priviledge..



    SQL> select * from brnch_mstrv;

    brnch brnch_name
    ----- ----------
    b001 alpha
    b002 beta
    b003 gamma
    b005 delta
    b006 theta
     
  10. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    The error was on table EMP_MSTRV try running this code:
    Code (SQL):
    SELECT * FROM USER_TAB_PRIVS_RECD WHERE TABLE_NAME = 'EMP_MSTRV'
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    He can describe both tables therefore he has select privilege on both. The error states 'invalid identifier', not 'table or view does not exist' which would be displayed in the absence of select privilege.

    Using the commands provided the tables create without error and selects against them run error-free:

    Code (SQL):
     
    SQL> CREATE TABLE brnch_mstrv("brnch_no" VARCHAR(5) PRIMARY KEY,"brnch_name" varchar2(10));
    TABLE created.
    SQL>
    SQL> INSERT ALL
    2 INTO brnch_mstrv VALUES ('b001','alpha')
    3 INTO brnch_mstrv VALUES ('b002','beta')
    4 INTO brnch_mstrv VALUES ('b003','gamma')
    5 INTO brnch_mstrv VALUES ('b005','delta')
    6 INTO brnch_mstrv VALUES ('b006','theta')
    7 SELECT * FROM dual;
    5 ROWS created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> CREATE TABLE emp_mstrv("emp_no" VARCHAR(5) PRIMARY KEY,"f_name" varchar2(10),
    2 "l_name" varchar2(10),"dept" varchar2(10),"desg" VARCHAR(5),"brnch_no" VARCHAR(5));
    TABLE created.
    SQL>
    SQL> INSERT ALL
    2 INTO emp_mstrv VALUES('e0001','Bleezo','Snarm','Rubbish','Elmo','b001')
    3 SELECT * FROM dual;
    1 ROW created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT e."emp_no",e."f_name",b."brnch_name" FROM emp_mstrv e,brnch_mstrv b
    2 WHERE b."brnch_no" = e."brnch_no";
    emp_n f_name brnch_name
    ----- ---------- ----------
    e0001 Bleezo alpha
    SQL>
     
     
    I expect the original emp_mstrv table has a backspace or some other control character buried in the column name brnch_no causing this error. Try dropping emp_mstrv and recreating it with the command you posted.
     
  12. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Sorry for the wrong information i posted then. I have said that because i created those tables in my schema end inserted those the records then i issue the select statement and found no error. That's why i thought it is really privilege issues. Again sorry for misleading post. I just tried to help.
     
  13. vikumar

    vikumar Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    thanks...David..it worked....
    but need a little more help...

    does "select * from dual" does anything good in this query...?
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    INSERT ALL requires a select for correct syntax; using dual is the simplest way to complete that statement. It won't work without the select.

    Using INSERT ALL allows multiple tables to be inserted into in one transaction. so I could have included both tables in the INSERT ALL statement and loaded them at the same time.
     
  15. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No need for apology, I was simply pointing out that your logic was incorrect. Oracle got past the FROM clause and found no access issues; the error only surfaced when trying to find the brnch_no column in both tables.
     
  16. vikumar

    vikumar Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    thanks.....