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?
Yours,
Laurenz Albe
