Re: problem with a conditional statement

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.