This discussion is archived
7 Replies Latest reply: Nov 25, 2012 11:45 AM by Satyaki_De RSS

Strange Behaviour

877802 Newbie
Currently Being Moderated
Hi

I have created two tables temp_n and temp_c
1) DESC TEMP_N

Name    Null    Type      
--------    -----     ----- 
COL2         NUMBER 

2) DESC TEMP_C

Name    Null    Type      
--------    -----     ----- 
COL3             NUMBER(1)

3)  SELECT * FROM TEMP_C
    ----------------------------------------

COL3
--------
       1
       2

SELECT * FROM TEMP_N
----------------------------------------

COL2
--------
       4
       5
       6


SELECT * FROM  TEMP_N WHERE COL2 IN (SELECT COL2 FROM TEMP_C); 

When i ran the above query, i see the below output instead of throwing error "ORA-00904: "COL2": invalid identifier"

COL2
--------
       4
       5
       6
can anyone explain why i am seeing the above output, instead of throwing error.

When i created the tables with test and test_1 using same definition instead of using temp_n and temp_c, and ran below query, i am seeing below error
SELECT * FROM  TEST WHERE COL2 IN (SELECT COL2 FROM TEST1); 

ORA-00904: "COL1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 12 Column: 27
Thanks,
Niranjan.

Edited by: 874799 on Nov 25, 2012 11:16 AM
  • 1. Re: Strange Behaviour
    sb92075 Guru
    Currently Being Moderated
    how can we reproduce what you report?


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Strange Behaviour
    Satyaki_De Guru
    Currently Being Moderated
    Probably you have created the tables using any tool which generates the script using double quotes.

    Whenever you put anything using double quotes - It become case sensitive.

    Kindly find the following demonstration -
    SATYAKI>
    SATYAKI>select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    Elapsed: 00:00:00.62
    SATYAKI>
    SATYAKI>
    SATYAKI>create table ttt
      2      (
      3        col1   number,
      4        col2   char(1)
      5     );
    
    Table created.
    
    Elapsed: 00:00:00.28
    SATYAKI>
    SATYAKI>insert into ttt values(1,'X');
    
    1 row created.
    
    Elapsed: 00:00:00.26
    SATYAKI>
    SATYAKI>commit;
    
    Commit complete.
    
    Elapsed: 00:00:00.26
    SATYAKI>
    SATYAKI>select * from ttt;
    
          COL1 C
    ---------- -
             1 X
    
    Elapsed: 00:00:00.48
    SATYAKI>
    SATYAKI>drop table ttt;
    
    Table dropped.
    
    Elapsed: 00:00:00.76
    SATYAKI>
    SATYAKI>create table ttt
      2      (
      3        "col1"   number,
      4        "col2"   char(1)
      5     );
    
    Table created.
    
    Elapsed: 00:00:00.28
    SATYAKI>
    SATYAKI>insert into ttt values(1,'X');
    
    1 row created.
    
    Elapsed: 00:00:00.28
    SATYAKI>
    SATYAKI>select * from ttt;
    
          col1 c
    ---------- -
             1 X
    
    Elapsed: 00:00:00.54
    SATYAKI>
    SATYAKI>select col1,
      2            col2
      3     from ttt;
              col2
              *
    ERROR at line 2:
    ORA-00904: "COL2": invalid identifier
    
    
    Elapsed: 00:00:00.51
    SATYAKI>
    SATYAKI>select COL1, COL2
      2     from ttt;
    select COL1, COL2
                 *
    ERROR at line 1:
    ORA-00904: "COL2": invalid identifier
    
    
    Elapsed: 00:00:01.07
    SATYAKI>
    SATYAKI>select "col1", "col2"
      2     from ttt;
    
          col1 c
    ---------- -
             1 X
    
    Elapsed: 00:00:00.51
    SATYAKI>
    SATYAKI>
    Check your DDL script & remove the double script or invoke your select statement using double quotes as it was exactly mentioned inside in your script. :)

    Regards.

    Satyaki De.
  • 3. Re: Strange Behaviour
    Hoek Guru
    Currently Being Moderated
    It is because you did not alias your tables and column names.
    If you do not do that, COL2 from your inner query is considered as belonging to table TEMP_N instead of TEMP_C.
    Try:
    SELECT a.* FROM  TEMP_N a WHERE a.COL2 IN (SELECT b.COL2 FROM TEMP_C b); 
  • 4. Re: Strange Behaviour
    Satyaki_De Guru
    Currently Being Moderated
    Hey!

    I missed one part ->

    Your TEMP_C doesn't have any COL2 column.

    Check your DDL that you have posted.

    Obviously, As suggested by Hoek - you have to use column alias.

    Regards.

    Satyaki De.
  • 5. Re: Strange Behaviour
    phaeus Pro
    Currently Being Moderated
    Hello,
    you have lowercase column name in you duoble quotes. If you write it upper or leave them without double quotes you can select col1. Other option is to use it also in you select.

    SQL> select col1 from ttt;
    select col1 from ttt
    *
    ERROR at line 1:
    ORA-00904: "COL1": invalid identifier


    SQL> select "col1" from ttt;

    col1
    ----------
         1

    SQL>


    regards
    Peter
  • 6. Re: Strange Behaviour
    877802 Newbie
    Currently Being Moderated
    I have re-created test case again with working and non working scenario and below are the two sql's and i don't want to use any alias names here :



    Working SQL's:
    ---------------------
    
    CREATE TABLE TEM_C(COL1 NUMBER(1));
    
    table TEM_C created.
    
    CREATE TABLE TEM_P(COL1 NUMBER(1));
    
    table TEM_P created.
    
    INSERT INTO TEM_C VALUES(1);
    1 rows inserted.
    
    INSERT INTO TEM_C VALUES(2);
    1 rows inserted.
    
    
    INSERT INTO TEM_P VALUES(3);
    1 rows inserted.
    
    INSERT INTO TEM_P VALUES(4);
    1 rows inserted.
    
    INSERT INTO TEM_P VALUES(5);
    1 rows inserted.
    
    SELECT * FROM TEM_P
    
          COL2
    ----------
             3 
             4 
             5 
    
    SELECT * FROM TEM_C
    
          COL1
    ----------
             1
    
    SELECT * FROM TEM_C WHERE COL1 IN (SELECT COL1 FROM TEM_P);
    
    ORA-00904: "COL1": invalid identifier
    00904. 00000 -  "%s: invalid identifier"
    *Cause:    
    *Action:
    Error at Line: 22 Column: 27
    Not working SQL
    -----------------------
    
    CREATE TABLE TEMP1(COL1 NUMBER(1));
    
    table TEMP1 created.
    
    CREATE TABLE TEMP2(COL1 NUMBER(1));
    
    table TEMP2 created.
    
    INSERT INTO TEMP1 VALUES(1);
    1 rows inserted.
    
    INSERT INTO TEMP1 VALUES(2);
    1 rows inserted.
    
    
    INSERT INTO TEMP2 VALUES(3);
    1 rows inserted.
    
    INSERT INTO TEMP2 VALUES(4);
    1 rows inserted.
    
    INSERT INTO TEMP2 VALUES(5);
    1 rows inserted.
    
    SELECT * FROM TEMP2
    
          COL2
    ----------
             3 
             4 
             5 
    
    SELECT * FROM TEMP1
    
          COL1
    ----------
             1 
             2 
    
    SELECT * FROM TEMP1 WHERE COL1 IN (SELECT COL1 FROM TEMP2);
    
          COL1
    ----------
             1 
             2 
    Why in first case it is working and why it is not working in second case, please explain ?

    I gave outputs from both scenarios.

    Thanks,
    Niranjan.
  • 7. Re: Strange Behaviour
    Satyaki_De Guru
    Currently Being Moderated
    Duplicate post.

    Please close one.

    Satyaki De.

Legend

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