On May 9, 2007, at 10:41 AM, Erik Jones wrote:
>
> 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".
Got it! Thankyou!
>
> erik jones <erik@myemma.com>
> software developer
> 615-296-0838
> emma(r)
>
>
>