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;
>>
> 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.
I did not provide the complete statement because
a) I am lazy and
b) I didn't want to create the impression that it was bulletproof
tested SQL :^)
> 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?
Yes!
>> 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
Sorry for being so lazy :^)
Here is a more elaborate version, I'm trying to add 'avgsol' to
your original FROM clause:
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?
Yours,
Laurenz Albe
