Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: Query with string without using ''?

    I have two queries with both the same result in Oracle 9. The field STAT_VER is a Varchar2(3) but in the query no '' are necessary for STAT_VER=2. The result with using '' is the same. Why? I can imagine that using Oracle 9 this works fine but using Oracle 8 not? Is that true?

    The two queries:

    select count (*) from gls_locatie
    where (gls_locatie.na1987 <> 'N' or gls_locatie.na1987 is null) AND (gls_locatie.TYPE <> 'W' or gls_locatie.TYPE is null) AND gls_locatie.eigenaar IN (SELECT DATA FROM gls_tabellen WHERE code = 'EIGENAAR')
    and jaar_EUT='2003'
    AND gls_locatie.stat_ver =2 and (gls_locatie.soort_urg='Nee' or gls_locatie.soort_urg is null)

    select count (*) from gls_locatie
    where (gls_locatie.na1987 <> 'N' or gls_locatie.na1987 is null) AND (gls_locatie.TYPE <> 'W' or gls_locatie.TYPE is null) AND gls_locatie.eigenaar IN (SELECT DATA FROM gls_tabellen WHERE code = 'EIGENAAR')
    and jaar_EUT='2003'
    AND gls_locatie.stat_ver ='2' and (gls_locatie.soort_urg='Nee' or gls_locatie.soort_urg is null)

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle implicitly performs conversion from number into varchar2 datatype when it is possible. Therefore, there's no need for you to use TO_CHAR function here.
    As far as I know, there's no difference between Ora8 and Ora9 regarding this issue.

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Maybe a difference between Ora7 and Ora9?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I know, there's no difference between Ora7 and Ora9 regarding this issue.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Actually, the implicit conversion goes the other way - i.e. the VARCHAR2 is implicitly converted to NUMBER. This example demonstrates:

    SQL> select * from emp where ename=2;
    select * from emp where ename=2
    *
    ERROR at line 1:
    ORA-01722: invalid number

    Presumably different behaviour is being seen because in the 9i database there are no non-numeric STAT_VER values (e.g. 'X'), whereas in the 8i database it happens that there is at least one. It has nothing to do with the versions per se.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This also shows why it is always a bad idea to reply on implicit conversions:
    a) you may have a false idea of what Oracle will do
    b) you may get ORA-01722 errors
    c) the implicit TO_NUMBER on the column prevents Oracle from using an index on it.

    We have a big table here where the primary key is inexplicably defined as VARCHAR2(12) but is populated from a sequence. If I had a penny for every time I type "select * from big_table where id = 12345" and then wonder why it takes several seconds I'd have... well, several pennies anyway.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I have always wondered how Oracle do this, the implicit conversions. But as a matter of fact, I dont relay on implcit conversions, I always *do it* by myself as needed. What I guess that happens underneath, is that if you let the Engine guess for you, it will take *more* time, since somehow it must perform "ok, he's trying to compare an apple to a orange, so im gonna cast the apple as an orange because that's what he wants", instead if I do the conversion by myself, I am saving time on the engine to guess. This is just my oppinion and I have no theory or anything to back it up, thus allow corrections, will appreciate these.

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are right to always "do it yourself" via explicit conversions. But if you want to know the rules for implicit conversions, they are documented here in the SQL Reference - search for "Implicit Data Conversion". For example:
    - When comparing a character value with a NUMBER value, Oracle converts the character data to NUMBER.

Posting Permissions

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