Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding ORA-01722 Invalid Number

Re: Avoiding ORA-01722 Invalid Number

From: Geoff Muldoon <gmuldoon_at_trap.scu.edu.au>
Date: Tue, 7 Jun 2005 17:24:28 +1000
Message-ID: <MPG.1d0ff5a5325a6f32989680@news.aioe.org>


newspostings_at_hazelweb.co.uk says...

> For the following, please don't tell me to 'change my design' - I just
> want to know whether there is a way of coding the select such that it
> won't return an error.....

> Now I can see that Oracle is objecting because we are asking it to
> compare values in the selected columns to numbers (we can change the
> BETWEEN clause thus: "BETWEEN '20' and '30'" and this would run without
> error.
>
> However a value of '021' would not fit within this range because it is a
> 3 character string rather than a true number.

SELECT * FROM t1 WHERE
(CASE
  WHEN
   [you work out the logic to detect any non-numericals in charcol, maybe using INSTR(TRANSLATE ...), maybe other methods, your ruleset/logic wasn't all that clear - is 021 in or out? .. this could be the trickiest bit .. is this homework?]

    THEN
     null
  ELSE
     TO_NUMBER(charcol) -- depending on above TO_NUMBER may be overkill   END)
BETWEEN 20 AND 30; Geoff M Received on Tue Jun 07 2005 - 02:24:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US