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
>
>
