7 Replies Latest reply: Nov 25, 2012 1:45 PM by Satyaki_De RSS

    Strange Behaviour

    877802
      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
          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
            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
              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
                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
                  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
                    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
                      Duplicate post.

                      Please close one.

                      Satyaki De.