On May 9, 2007, at 10:32 AM, Kirk Wythers wrote:
>
>>
>> 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?
The "AVG(par)" should've been aliased. "AVG(par) as par" would
work. As is, the column name returned is just "avg".
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)