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