Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Lightbulb Unanswered: I dont know why?

    1 BEGIN
    2 IF '20' between '100' AND '401' THEN
    3 DBMS_OUTPUT.PUT_LINE('INSIDE IF');
    4 ELSE
    5 DBMS_OUTPUT.PUT_LINE('OUTSIDE IF');
    6 END IF;
    7* END;
    test@morcl.us> /
    INSIDE IF
    PL/SQL procedure successfully completed.

    Why it is inside if loop? Why oracle is not converting it to number?

    Thanks
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Should it convert characters into numbers implicitly? What if you had something like
    IF 'A123' BETWEEN 'c43' and 'z24' THEN ... ? I'm not sure, but I guess Oracle won't bother guessing if it could convert it or not, but simply deals with data as they are (in this case, as characters).


    IF TO_NUMBER('20') BETWEEN TO_NUMBER('100') AND TO_NUMBER('401') THEN ...

    says "outside if", just as you'd like it to.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That's right: Oracle only performs an implicit TO_NUMBER conversion if it has to because of conflicting data types - e.g. "IF '20' BETWEEN 100 AND 401" or "IF 20 BETWEEN '100' AND '401'". If all your values are character strings, Oracle sensibly treats them as such.

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi,

    Thanks fro the reply!!

    I know it will work when i convert string to number.
    I have wasted around 5 hours for such a small thing.

    Littelfoot & Andrewst:
    Here oracle is converting string to number and adding?
    This was in my mind and i dont even bother to convert string to number in my query.

    test@morcl.us> select '20' + '30' from dual;

    '20'+'30'
    ----------
    50


    Thanks,
    Pagnint
    (No need to search web before posting new question)

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In this case, the conflict is between the operator "+", which requires 2 numbers, and the operands '20' and '30' which are strings. Since addition doesn't work for strings, Oracle implicitly converts them to numbers for you.

    However, "a BETWEEN b AND c" is really a shorthand for "(a >= b AND a <= c)", and the operators "<=" and ">=" are valid for strings as well as for numbers, but behave differently. For strings, Oracle performs a lexical comparision based on the collating sequence (...,'0','1',...,'A','B','C',...,'a','b',...); for numbers Oracle performs a numerical comparison.

    So although confusing, it is quite logical!

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    By doing implicit conversions (as in the '20'+'30' example), Oracle is trying to be helpful: "the programmer asked me to add together 2 strings, which does not make sense; perhaps he wants me to convert them to numbers first? OK I'll try to do that..."

    It would perhaps be better if such implicit conversions were not done, and instead you got an error like "improper data type" instead. If you wanted numbers you would have to explicitly TO_NUMBER the values yourself.

  7. #7
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    As usual : Tony is great !!! Thanks for a very good explanation

    Please don't come to India or else my job will be in Danger


    Thanks,
    Pagnint
    (No need to search web before posting new question)

Posting Permissions

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