Re: Select numeric data fom varchar but in a range of values

Stuart Smith wrote:
>
> I have a varchar cloumn that can contain both numeric and alphanumeric
> data.
>
> The following query works OK to select numeric data only between a
> range of values
>
> select aircraft_reg from flight_leg
> WHERE to_number(aircraft_reg) BETWEEN 9000 AND 9999
> AND instr(translate(upper(aircraft_reg), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
> 'XXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X') = 0
>
> but if I transpose the two lines of the WHERE clause thus
>
> select aircraft_reg from flight_leg
> WHERE instr(translate(upper(aircraft_reg),
> 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', 'XXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X') = 0
> AND to_number(aircraft_reg) BETWEEN 9000 AND 9999
>
> then I get ORA-01722 invalid number.
>
> This appears to be because ORACLE evaluates the WHERE clause from the
> last condition to the first condition.
>
> Obviously, I could leave the code as the first select above but I
> would like to write some SQL that is compeletely independent of the
> ORACLE SQL parser/evaluator.
>
> Any suggestions??
>
> Thanks

It seems to me that the real problem is in your database design. A column in a relational database should contain data belonging to only one domain. At the minimum, this means that the data should be numeric, or character, but not both (character data which happens by chance to contain only numbers is still character).

Much of my time is spent with a data warehouse which takes as input data from an old IMS database which was designed to have certain fields contain data in all sorts of crazy formats, with program logic determinine exactly how it was to be interpreted. One could excuse that, on the grounds that the IBM 360-45 did it that way and 'why bother to reinvent the wheel'?

I found the best way to handle my warehouse is to split things up into multiple columns at load time.

If you cannot split the data into two columns I suggest you write a function to evaluate the column, and call it every time you need data from that column. There will be a performance cost. The problem with doing it in the WHERE clause is that there is no way to predict how the next version of the optimizer will behave.

Hope these thoughts help.

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

My other .sig contains the cure for cancer