to_number question 2004-07-15 - By Stephen.Lee@(protected)
> -- --Original Message-- --
> You wouldn 't happen to have CREATE TABLE statement handy,
> with some INSERTs to generate some sample data, would you?
> I 'd really like to recreate your scenario on my own system.
I do not. The char(18) columns nbr_cc_no_low and nbr_cc_no_high hold number
ranges. Apparently, there are (or have been) oddball ranges that use
letters. Either that, or the column has some ad hoc entries somebody stuck
in there for some reason or another (somebody slap that guy). The
nbr_cc_fop_name is a varchar2(6). The two columns nbr_cc_no_low and
nbr_cc_no_high work with the nbr_cc_fop_name to define valid ranges for a
particular type nbr_cc_fop_name. The table itself has 50388 rows.
Approximately 100 of the rows have entries that will break to_number. The
rows of interest -- those where nbr_cc_fop_name in ( 'AX ', 'VI ', 'MC ', 'DS ') --
make up 13 rows, and they are all valid for to_number. I have a feeling
that a BIG part of what is tripping me up is the fact that there is an index
on nbr_cc_no_high but not on nbr_cc_fop_name (and not nbr_cc_no_low ...
don 't look at me, I didn 't design this). I suspect that the optimizer has
fallen in love with that juicy, ripe index.
The p_corigid are values coming from another table. What I have to do is
populate dev and test with something like:
copy from joe/blow@(protected) insert xyz using select this, that, doink(corigid),
some_more from schema.xyz;
And for you fans of normalization, you might notice that the doink function
has to apply the dummy numbers to only part of the "other " argument while
leaving part of it unchanged. Which should tell you that this single column
contains more that one piece of info. Ain 't that great! (I just work
here.)
> because when I tried to execute it I
> received an error that made me realize that
> his query should not work at all.
I swear I can hear the Twilight Zone theme song playing.
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
