in reply to Re: DBI: when 1 != '1'
in thread DBI: when 1 != '1'

In Oracle:
create table rdf_test(v1 number);
insert into rdf_test values(1);
select count(*) from rdf_test where v1 = 1;

COUNT(*)
========
       1

select count(*) from rdf_test where v1 = '1';

COUNT(*)
========
       1

select count(*) from rdf_test where v1 = '1.0';

COUNT(*)
========
       1

[download]

rdfield

Replies are listed 'Best First'.
Re: Re: Re: DBI: when 1 != '1'
by tommyw (Hermit) on Sep 27, 2002 at 14:21 UTC

    Yes, it look's like the string parameter gets converted to a number. Told you it had changed. ;-)

    Which means that 

    SQL> select count(*) from rdf_test where '1.0' = v1;

  COUNT(*)
----------
         1

SQL>  select count(*) from rdf_test where 'a' = v1;
 select count(*) from rdf_test where 'a' = v1
                                     *
ERROR at line 1:
ORA-01722: invalid number


SQL> select count(*) from rdf_test where v1 = 'a';
select count(*) from rdf_test where v1 = 'a'
                                         *
ERROR at line 1:
ORA-01722: invalid number

    [download]

    Obviously the fix is to convert the number into a string (since the exception can't be generated then). Thus, 

      1* select count(*) from rdf_test where 'a' = TO_CHAR(v1)
SQL> /

  COUNT(*)
----------
         0

    [download]
    Great. It's working fine now. But... 
    
SQL> select count(*) from rdf_test where '1.0' = TO_CHAR(v1);

  COUNT(*)
----------
         0

    [download]
    which leaves it still being a headache.

    --
    Tommy
    Too stupid to live.
    Too stubborn to die.

[reply]
[d/l]
[select]

In Section Meditations