On May 9, 2007, at 10:32 AM, Kirk Wythers wrote:
Here is a more elaborate version, I'm trying to add 'avgsol' to
your original FROM clause:
SELECT CASE WHEN w.station_id = site_near.station_id THEN
w.obs_id ELSE
s.obs_id END AS obs_id, site_near.station_id, site_near.longname,
w.year, w.doy, w.precip, w.tmin, w.tmax,
--replace missing values (-999) with the monthly average
CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END
FROM site_near
INNER JOIN solar s ON
(site_near.ref_solar_station_id = s.station_id
AND site_near.obs_year = s.year)
INNER JOIN weather w ON
(site_near.ref_weather_station_id = w.station_id
AND site_near.obs_year = w.year
AND s.date = w.date)
INNER JOIN (SELECT month, AVG(par) FROM solar GROUP BY month) AS
avgsol ON
(s.month = avgsol.month)
WHERE ...
Still no claim for correctness.
Does it make more sense now?
Thanks again Laurenz. Hopefully I have nearly figured this out. I
have a question that indicates to me that I am a little fuzzy on
one more point.
In the line: "CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END"
I am getting an error that says, "column avgsol.par does not
exist". I understand that you are creating avgsol as a subselect,
but I also see the point of the error message that the column .par
does not exist. If I change avgsol.par to the simple form avgsol
(to match the subselect " INNER JOIN (SELECT month, AVG(par) FROM
solar GROUP BY month) AS avgsol ON (s.month = avgsol.month)". Then
I get an error about CASE types real and record cannot be matched.
Any final ideas?
The "AVG(par)" should've been aliased. "AVG(par) as par" would
work. As is, the column name returned is just "avg".
erik jones <erik@xxxxxxxxxx>
software developer
615-296-0838
emma(r)
