From: | Kirk Wythers <kwythers(at)umn(dot)edu> |
---|---|
To: | Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: problem with a conditional statement |
Date: | 2007-05-09 15:32:00 |
Message-ID: | E1550243-4250-45E1-A0AE-6E67CF9CAED4@umn.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> 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?
>
> Yours,
> Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-05-09 15:34:58 | Re: In theory question |
Previous Message | Erik Jones | 2007-05-09 15:30:11 | Re: In theory question |