Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding ORA-01722 Invalid Number
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