| 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 (excellent thread)
I'll second that.
Without begging the question either way, I think there is a doctoral thesis out there for someone who figures out the implications of a new value classification "BAD" that is different from NULL in that it is known to have violated agreed information content manipulations from its source.
I think Dan Tow pointed out a potential upside, that if tuples containing BAD values were filtered out before final projection, then it could operate in harmony with independence of information from its access and storage and route of projection and yet allow projection of results that would otherwise die due to error along the projection path actually used.
I think Wolfgang Breitling pointed out that this is an entirely new burden to foist upon the database engine, which proper architecture, design, types, and data cleansing could factor out.
I think I agree with both of them, but that the jury is out... no scratch that ... it is premature upstream of basic feasibility research to assign a jury ... it is not yet decided whether the introduction of a BAD value in tuples would result in an improvement in information processing systems.
This harkens back to the very old question from *many* sources, "So why can't to_number just give us a NULL for bad numbers?" Or why not zero? Oops. So you can write a function that gives you an answer you prefer operating on your information in the context you know, but Oracle doesn't get to supply any definitive answer (not even definitely NULL, ie. undefined) when an error occurs. Now in particular for nto_number and zto_number it might be convenient and certainly the error trap conversion would be faster in the database engine if nto_number yielded nulls for bad conversions and zto_number yielded zeroes, but if you expand out the possible "good" choices for error answer assignment to all the convenience functions that can terminate in error given bad data, then my guess is that it is an unmanageable number. (I've already left out -1, "high_values", and "low_values" even for xxxto_number.) I suppose one might convince Oracle to create a generic to_number where you supply the value to be returned for "bad" values, but where that would fit in the priority list is an interesting question, especially since you can effectively do that yourself.
Still, there is a ton of raw data out there that we'd like to collect and constraint later. Once we store it, someone wants to query it, and the thing we accepted that really should be numeric but we let masquerade as a string because we wanted to collect the rest of the tuple at all becomes a problem.
If anyone has seen any body of work on this subject, I'd love a referral to it.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Daniel Fink
Sent: Friday, July 16, 2004 10:17 AM
To: oracle-l_at_freelists.org
Subject: Re: to_number question
Wow, what a thread! This is what this list is about. The respectful exchange of ideas, regardless of agreement or disagreement. Someone once said that you learn most from those who disagree with you. (Of course, the occasional dash of humor adds a little spice).
On the subject of subqueries, we must also consider views, which is a subquery with a stored definition. I remember being taught that a view was a logical abstraction and that was it. If we extend the argument that we must consider optimization when executing a subquery, we must do the same for a view. I would venture to guess that there are many more statements using views than coded subqueries. And all the rules of data integrity, performance, optimizer transformation apply. And, yet, how often do we (including anyone who writes code that accesses the database) consider these implications when putting together a view and using in a query.
This is something that requires more thought (from me at least).
Regards,
Daniel Fink
 |
 |