Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Posts
    294

    Post Unanswered: Function In The Where Clause

    Hello, when I execute this query in Oracle 8i I get no errors, but when I try to do It in Oracle 9i, I get the error: ORA-01722: invalid number, cause the column DSVALOR_ACTUAL has varchar values, why does oracle 8i accept those values and 9i doesn't? Is is a new feature, or is there any patch ?

    SELECT *
    FROM TOSI_HISTORICO_MODI
    WHERE TO_NUMBER(DSVALOR_ACTUAL) = 0;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It doesn't work in 8i either. If your varchar2 field only contained numeric values, the to_number would work fine, however if it contains any alpha characters and they are not excluded by other methoids, it will give you that error. You must have different data between your 8i machine and your 9i machine.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The difference will not be the Oracle versions, it will be the data. In your 9i database you apparently have some non-numeric data in that column. See this (on 8i):
    Code:
    SQL*Plus: Release 8.0.6.0.0 - Production on Mon Sep 27 09:55:58 2004
    
    (c) Copyright 1999 Oracle Corporation.  All rights reserved.
    
    
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.3.0 - Production
    
    SQL> create table TOSI_HISTORICO_MODI ( DSVALOR_ACTUAL varchar2(10) );
    
    Table created.
    
    SQL> insert into TOSI_HISTORICO_MODI values ('x');
    
    1 row created.
    
    SQL> SELECT *
      2  FROM TOSI_HISTORICO_MODI
      3  WHERE TO_NUMBER(DSVALOR_ACTUAL) = 0;
    WHERE TO_NUMBER(DSVALOR_ACTUAL) = 0
          *
    ERROR at line 3:
    ORA-01722: invalid number

  4. #4
    Join Date
    Jun 2003
    Posts
    294
    Well, I I'm trying to execute this Query:
    SELECT *
    FROM TOSI_HISTORICO_MODI
    WHERE TO_NUMBER(DSVALOR_ACTUAL) = 0
    AND CDCAMPO = 'POMESADA'

    It works In 8i but not In 9i. All the non numeric rows are excluded by the where clause in 8i and in 9i too. It means that the function TO_NUMBER is only using numeric values in both cases, but the column has varchar values (that are filtered in CDCAMPO = 'POMESADA' condition).

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by jortiz
    Well, I I'm trying to execute this Query:
    SELECT *
    FROM TOSI_HISTORICO_MODI
    WHERE TO_NUMBER(DSVALOR_ACTUAL) = 0
    AND CDCAMPO = 'POMESADA'

    It works In 8i but not In 9i. All the non numeric rows are excluded by the where clause in 8i and in 9i too. It means that the function TO_NUMBER is only using numeric values in both cases, but the column has varchar values (that are filtered in CDCAMPO = 'POMESADA' condition).
    I suspect that you had rule based in 8I. In rule base, it would work your where clause from the back forward. I suspect that if you look at your data, all rows with the CDCAMPO='POMESADA' will only be numeric. Under cost base, it decided to check the column DSVALOR_ACTUAl first. Try re-writting your query to somethin like.

    SELECT *
    FROM (select * fromTOSI_HISTORICO_MODI
    where CDCAMPO = 'POMESADA') a
    WHERE TO_NUMBER(a.DSVALOR_ACTUAL) = 0
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2003
    Posts
    294
    In the optimizer mode of I have choose, In 9i and in 8i, and you're right when you say that all rows with the CDCAMPO='POMESADA' are always numeric for both (8i and 9i) and the Query that you sent me still has the problem.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, that wasn't the query you said you were running earlier!

    OK, so 8i happens to be processing the CSCAMPO condition first, and 9i is not. With the CBO, the order of processing is not guaranteed, so this query cannot be guaranteed to work (in 8i or 9i). With the RBO, the conditions are applied from bottom up, and so reversing them (in 8i):
    Code:
    SQL> SELECT *
      2  FROM TOSI_HISTORICO_MODI
      3  WHERE CDCAMPO = 'POMESADA'
      4  AND TO_NUMBER(DSVALOR_ACTUAL) = 0
      5  /
    AND TO_NUMBER(DSVALOR_ACTUAL) = 0
        *
    ERROR at line 4:
    ORA-01722: invalid number
    So applying a /*+ RULE */ hint in 9i may make your query work.

    My guess is you have one of those ugly Entity-Attribute-Value tables here, which is a pity. Numbers should be stored in NUMBER columns, and then such problems don't arise!

    A more robust work-around may be to create your own version of TO_NUMBER, which returns 0 or -1 or whatever for non-numeric values:
    Code:
    create or replace function my_to_number( p_val in varchar2 )
    return number
    is
    begin
      return to_number(p_val);
    exception
      when others then
        return 0;
    end;

  8. #8
    Join Date
    Jun 2003
    Posts
    294
    Yes, Perfectly !!!!

  9. #9
    Join Date
    Apr 2004
    Posts
    246
    This one comes up constantly. First, I agree that the best solution is to store number as numbers, dates as dates, etc. Second, if you know that the column contains characters, why use the to_number function? Also, it prevents an index from being used (assuming you don't have fbi's). Remove the function, make the zero a string:

    SELECT *
    FROM TOSI_HISTORICO_MODI
    WHERE DSVALOR_ACTUAL = '0'
    AND CDCAMPO = 'POMESADA'
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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