Kirk Wythers wrote:
> I am struggling to get a CASE WHEN statement to work within another
> CASE WHEN. Here is my original code:
>
> 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 solar values (-999) with the average of all solar
> --values from that month (s.month)
>
> --CASE s.par WHEN -999 THEN AVG( s.par) ELSE s.par END
> --FROM solar s
> --GROUP BY s.month;
>
> 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
> WHERE w.station_id = 211630;
>
> I have commented out the troublesome bits in the middle of the code.
> All I am trying to do here is to replace missing values with averages
> from the same day of the year for all years. Does anyone see what I
> am buggering up here?
The problem here is the AVG().
All columns that appear outside of group functions in the SELECT list
must be in the GROUP BY clause.
Maybe something like this could help you:
SELECT ..., w.tmax,
CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END
...
FROM solar s INNER JOIN ...,
(SELECT month, AVG(par) FROM solar GROUP BY month) AS avgsol
WHERE s.month = avgsol.month
AND ...
In this statement I create a subselect "avgsol" that I use like
a table.
Be warned that there will probably be a sequential scan of the whole
table "solar" whenever you run the statement, because the averages have
to be calculated first!
Yours,
Laurenz Albe