This discussion is archived
13 Replies Latest reply: Nov 25, 2012 10:42 PM by Ashu_Neo RSS

SQL 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: "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 Oracle ACE Director
    Currently Being Moderated
    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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Duplicate Post.

    Strange Behaviour

    Regards.

    Satyaki De.
  • 7. Re: SQL strange behaviour
    877802 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Sorry for the typo, i have modified the above question.
  • 13. Re: SQL strange behaviour
    Ashu_Neo Pro
    Currently Being Moderated
    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!

Legend

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