On May 8, 2007, at 2:02 AM, Albe Laurenz wrote:
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?
Thank you for the reply. I see what you are doing in the creating of
avgsol. That should work perfectly. However, I am unsure how you are
working it into the existing code.
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,
I think you adding "CASE s.par WHEN -999 THEN avgsol.par ELSE s.par
END" after
"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,"
to look this like this:
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
Correct?
CASE s.par WHEN -999 THEN avgsol.par ELSE s.par END
...
FROM solar s INNER JOIN ...,
I can't quite figure out what you are suggesting here?
(SELECT month, AVG(par) FROM solar GROUP BY month) AS avgsol
WHERE s.month = avgsol.month
AND ...
Do you mean:
FROM site_near INNER JOIN solar s ON
(SELECT month, AVG(par) FROM solar GROUP BY month) AS avgsol WHERE
s.month = avgsol.month
AND 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 think my trouble is figuring how to place the code snipit:
(SELECT month, AVG(par) FROM solar GROUP BY month) AS avgsol
WHERE s.month = avgsol.month
AND ...
Sorry for being so dull
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!
That is ok, I won't be running this query so often that the
performance will be an issue.
