I'm not sure what you're trying to do but, it appears that you database
design is incorrect. What you need is something like
CREATE TABLE temp_readings
(
_date Date,
temperature double,
source varchar(20),
)
No reading, no record. Are you suggesting that you would have a weekly
set of records for each row?
CREATE TABLE temp_readings
(
weekstart date,
sun double,
mon double,
tues, double
etc
)
Not such a great way to do it.
Well, your mileage must vary. The absence of nulls would make my life
difficult.
Just substitute "unknown" for "null" as mentioned above and the various
operations with "null" make sense. For example, take some days and
low-temperatures:
Mon: 30
Tue: 10
Wed: 0
Thu: unknown
Fri: 0
Sat: unknown
Sun: -5
Was the low temperature the same on:
Mon/Tue: no
Wed/Fri: yes
Thu/Fri: unknown
Thu/Sat: unknown <- the always seemingly confusing null=null is null.
So what do we do without a null? Does the "helpful" app convert the
unknowns to zero? That's not right. Are we forced to specify a "special"
value like 999 for the unknown data? Then we have to add extra code to
create that value when the value is unknown and more code still to check
for that value when, say, looking for the lowest or average
temperatures. And we're set up for disaster when someone starts
measuring furnace temps instead of outdoor temps.
Look no further than Y2K to see what happened to those apps that gave
special meaning to 12/31/99.
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
