As for type conversions ... if you're doing type conversions, either your schema is wrong or you're not using it correctly. You should never ever have to use a type conversion, not even once. And, yes, that's a hard rule.
That's going a bit too far, don't you think? I'll try to think of a really good example... What about something like this, maybe:
select ... from table where table.day_in_month = to_number(to_char(sys
+date,'dd'))
Should I not be allowed to represent a day-in-month as a number? Should I, instead, be forced to use some sort of crazy "magic-number" reference month and year to store my day-in-month column, so that I can store it in a date datatype, and be able to do purely (but still crazy as all get-out) date-arithmetic to compare dates to the day-in-month? Imagine that the day-in-month is a piece of data used in representing a "recurrence" data-structure (like, "run this report on the 5th day of every month"), so I'm actually NOT talking about something that is a date... I'm talking about something more abstract, which can most easily be represented by type-casting (on top of some other logic).
Here's another kind of crazy (but real! I swear!) example: Another, really powerful use of type-casting in SQL comes up in a very fast (but admittedly ugly) way to pull data in one field by correspondance to data in another field. By that I mean, say, give me the X that corresponds to the min(Y). The specific case when I've used this with is trying to pull the earliest created value of something (say FOO is a number, for this example, and you want the FOO corresponding to the min(CREATED)):
select
to_number(
substr(
min(to_char(CREATED, 'yyyymmddhh24miss') || FOO)),
15, 400
)
)
from table
where ...
If that's a little hard to understand, that's not too surprising, as it's something analogous to the GRT, but for seeking a min or a max, rather than for sorting (as the GRT is just a special case of the ST for sorting). That is: pack the carrier data (CREATED) together with the payload data (FOO), in such a way as comparisons of the packed carrier+payload correspond to any comparisons made against the naked carrier (i.e. if rowX.CREATED < rowY.CREATED, then to_char(...rowX...) < to_char(...rowY...), accordingly). Then, use comparisons made against the packed carrier+payload combo, which will yield back the data in which you're interested, but as packed carrier+payload. And then, finally unpack the payload from the carrier (i.e. the to_number(substr(...)) construct).
Anyway, I know it's esoteric, but it is real, and it's WAY more efficient than the more purist SQL:
select FOO
from table
where CREATED = (select min(FOO) from table where ...)
and ...
particularly if there's some interesting stuff in that "..." part of the query (which has to be repeated in the sub-query). (Oh, and yes, I did purposefully gloss over a few details in that example, that weren't vital to getting the point accross.)
------------
:Wq
Not an editor command: Wq
| [reply]
[d/l]
[select] |
select ... from table where table.day_in_month = to_number(to_char(sysdate,'dd'))
SELECT ...
FROM table
WHERE DAYOFMONTH( NOW() ) = table.day_in_month
Your RDBMS should provide you with sufficient functions so as to make this unnecessary. And, though I don't have the reference in front of me, I'm pretty sure that Oracle does, just like MySQL and PostgreSQL both do.
Your second example is an optimization. Those are deliberate breakings of good practices (that reduce developer time) in order to gain in some other area (such as processor time). It's the classic tradeoff.
My criteria for good software:
- Does it work?
- Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
| [reply]
[d/l] |
So it's your contention that "DAYOFMONTH(...)" is not a type-conversion? I see one type going in (date) and a different type coming out (number). Or do you mean to say that only type-conversions that are built-in functions (but excluding the to_X(...) ones) are ok to use? Seems like an arbitrary line. Also, I doubt whether the makers of RDBMS have figured out every possible built-in type-converting function that might make sense to exist (good RDMSs allow developers to define their own functions for a reason).
As for the second, yes, I know full well that it's an optimization. An ugly one, in fact. The sort of thing that I don't even write out by hand, but have perl code generate for me. However, I don't think that makes it "wrong". Premature optimization is wrong, sure, but necessary optimization is... well... necessary. And it is hardly "wrong".
I think I'm sounding upset, and that's not how I mean it. I'm not upset at all, nor am I trying to turn this into a heated argument. I was just being pedantic about the absolutism of your statement that type-conversion is ALWAYS wrong. I heartily agree that type-conversion is *usually* wrong. But it's a lot less always wrong than goto, for example, and even goto has, like, one or two legitimate uses.
------------
:Wq
Not an editor command: Wq
| [reply]
[d/l] |
I agree that typing is a good thing for some applications, however I think the arguments against requiring typing in Perl could be made for a database. Yes, having constraints on the data that goes into the database is good for efficiency, reliability and performance - hometimes I'd like to optimize for development time.
For example, a small unimportant system that isnt going to hold much data, or a prototype that needs to be rapidly developed with a small investment in developer time.
A typeless relational database has (would have) applications.
And as for never having to use type conversions, what about shortening a string, or converting a numeric string to an int. Or making sure that the number is low enough to fit in a byte. etc etc.
| [reply] |