13 Replies Latest reply: Nov 26, 2012 12:42 AM by Ashu_Neo RSS

    SQL 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: "COL2": invalid identifier
      00904. 00000 -  "%s: invalid identifier"
      *Cause:    
      *Action:
      Error at Line: 12 Column: 27
      select banner from v$version
      
      Banner:
      Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      Thanks,
      Niranjan

      Edited by: 874799 on Nov 26, 2012 9:22 AM
        • 1. Re: SQL strange behaviour
          damorgan
          Please read the FAQ and learn how to format your listing with
           tags.
          
          After you do that perhaps someone will be able to help you because they will be able to read what you posted.
          
          Also, in your listing, put the DDL for creating the table and the DML for loading it with your sample data.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: SQL strange behaviour
            JustinCave
            1) This doesn't appear to have anything to do with database security. This would be more appropriate in the SQL and PL/SQL forum. Hopefully, one of the moderators will move it for you.
            2) If you format your code, it will be much easier for people to understand what you are asking
            3) If you are not using aliases, it can be easy to get confused about what table a particular column is coming from. In your case, in the inner query, COL2 is a valid identifier-- it just refers to the COL2 column in the TEMP_N table. So the query
            SELECT * 
              FROM TEMP_N 
             WHERE COL2 IN (
                SELECT COL2 
                  FROM TEMP_C); 
            is exactly equivalent to this
            SELECT * 
              FROM TEMP_N n
             WHERE n.COL2 IN (
                SELECT n.COL2 
                  FROM TEMP_C c); 
            In this case, so long as TEMP_C has at least one row, the inner query will return the current COL2 value from TEMP_N many times.

            Justin
            • 3. Re: SQL strange behaviour
              877802
              Then if i create an tables with test and test1 and ran below query i am seeing below error :
              SELECT * FROM  TEST WHERE COL2 IN (SELECT COL2 FROM TEST1); 
               
              ORA-00904: "COL2": invalid identifier
              00904. 00000 -  "%s: invalid identifier"
              *Cause:    
              *Action:
              Error at Line: 12 Column: 27
              Then why output is not coming in this case, why output is coming in case if i create table names with temp_n and temp_c like temp%(tables which starts which temp and also tried to create tem% i get error without any output can you please clarify
              • 4. Re: SQL strange behaviour
                damorgan
                No help is possible when you ignore the help you have already received.

                Please reread what Justin and I posted and act accordingly.

                Thank you.
                • 5. Re: SQL strange behaviour
                  Solomon Yakobson
                  874799 wrote:
                  Then why output is not coming in this case, why output is coming in case if i create table names with temp_n and temp_c like temp%(tables which starts which temp and also tried to create tem% i get error without any output can you please clarify
                  When you issue:
                  SELECT * FROM TEST WHERE COL2 IN (SELECT COL2 FROM TEST1);
                  Oracle tries to do name resolution to figure out what COL2 is. In the ballpark: since COL2 is referenced in subquery it checks if table TEST1 has column COL2 first. If it does, COL2 is resolved as TEST1.COL2. If not, since IN clause allows correlated subquery, it checks if table TEST has column COL2. If it does, COL2 is resolved as TEST.COL2. If not, Oracle checks if there is an function COL2 or a synonym COL2 that points to a function and if not raises ORA-00904. As you can see, it doesn't matter how tables are called. And based on ORA-00904 I can tell neither table has column COL2 and there is no functionsynonym to a function called COL2. So post yout create table statements for TEST and TEST1 and I bet there is no column COL2 in either of them.

                  SY.
                  • 6. Re: SQL strange behaviour
                    Satyaki_De
                    Duplicate Post.

                    Strange Behaviour

                    Regards.

                    Satyaki De.
                    • 7. Re: SQL strange behaviour
                      877802
                      Hi

                      I created the COL2 in Test. I clearly mentioned that created the tables with same columns but with different table names.

                      I don't understand why it is behaving for temp and can you please answer the questions in two ways
                      • 8. Re: SQL strange behaviour
                        rp0428
                        >
                        I don't understand why it is behaving for temp and can you please answer the questions in two ways
                        >
                        Well we don't understand why, after two requests, you won't edit your original post and add \{ccode} on the line before and the line after the code to preserve the formatting.

                        Your code is unreadable; all of those empty lines and _______________ lines are unnecessary.

                        And you still haven't provided your 4 digit Oracle version or posted the table DDL so people can try to reproduce your results.
                        • 9. Re: SQL strange behaviour
                          877802
                          Updated Oracle version and re-posted the question once again in below post with complete DDL statements too.

                          Edited by: 874799 on Nov 26, 2012 9:36 AM
                          • 10. Re: SQL strange behaviour
                            877802
                            Hi I tried once again with below test cases and same problem is coming :
                            Working SQL's:
                            ---------------------
                             
                            CREATE TABLE TEM_C(COL1 NUMBER(1));
                             
                            table TEM_C created.
                             
                            CREATE TABLE TEM_P(COL2 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
                            
                            
                            
                            Oracle Version
                            --------------------
                            
                            select banner from v$version
                            
                            Banner:
                            Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
                            PL/SQL Release 10.2.0.1.0 - Production
                            "CORE     10.2.0.1.0     Production"
                            TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
                            NLSRTL Version 10.2.0.1.0 - Production
                            Edited by: 874799 on Nov 26, 2012 11:33 AM
                            • 11. Re: SQL strange behaviour
                              Ashu_Neo
                              Hi,
                              it seems, you might have confused in what your doing and asking!
                              See you created table with one column name and your query retrieval was showing different column name. Like
                              1> CREATE TABLE TEM_P(COL1 NUMBER(1)); /* here it's COL1 */
                               
                              table TEM_P created.
                              . . . . . . .
                              And then your select query
                              2> SELECT * FROM TEM_P
                               
                                    COL2    /* here it is COL2 */ 
                              ----------
                                       3 
                                       4 
                                       5 
                              How is it possible!
                              But still if you change it later on, it will not throw an error as COL1 used in inner query will be treated as column of main query table.

                              Thanks!
                              • 12. Re: SQL strange behaviour
                                877802
                                Sorry for the typo, i have modified the above question.
                                • 13. Re: SQL strange behaviour
                                  Ashu_Neo
                                  And its working fine.
                                  SQL> select col1 from tem_c where col1 IN (select col1 from tem_p);
                                  
                                        COL1
                                  ----------
                                           1
                                           2
                                  As narrate by Justin in above replies.

                                  Thanks!