Query doubt
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query doubt

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    Query doubt

    Hi All,

    I did the follwoing test.

    Step 6 falied with ORA-01722. Can you please let me know why oracle failed to convert the number into character?

    1) create table xyz (col1 varchar2(20))

    2)
    insert into xyz values (1)
    insert into xyz values ('2')
    commit

    3)
    Following query works:
    ------------------------
    select * from xyz where col1 = 1
    select * from xyz where col1 = '2'

    4)
    insert into xyz values ('a')
    commit

    5)
    Following query works:
    ------------------------
    select * from xyz where col1 = '1'
    select * from xyz where col1 = '2'

    6)
    Following query does not works:
    ---------------------------------
    select * from xyz where col1 = 1
    Cheers!
    OraKid.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,459

    Cool


    Maybe because of this:
    Code:
    insert into xyz values ('a')

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by balajiyes
    Hi All,

    I did the follwoing test.

    Step 6 falied with ORA-01722. Can you please let me know why oracle failed to convert the number into character?

    6)
    Following query does not works:
    ---------------------------------
    select * from xyz where col1 = 1
    I hope now you learned how to treat the data type in Oracle.

    Tamil

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Quote Originally Posted by balajiyes
    Can you please let me know why oracle failed to convert the number into character?
    If you looked up the error message, you would understand why:

    ORA-01722 invalid number

    Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thanks Guys

    It worked in Step 3 and Why not in Step 6?

    Why this query did not fail in Step 3...?
    Cheers!
    OraKid.

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    If you put a numeric filter for char column, oracle converts char column values to numbers to compare. How would you convert 'a' to number?
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    because you need a full table scan and to_number is applied to all rows, when it meets your "a" it fails

    in step 3 since all rows can be "to_numbered" (no "a") so there is no error

    try this test

    create a non-unique index on col1, try your step 6 query

    then

    create an unique index on col1 and try again your step 6 query

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width