| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: to_number question
> -----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_at_prod 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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Jul 15 2004 - 09:29:39 CDT
 |
 |