Search Postgresql Archives

Re: problem with a conditional statement

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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@xxxxxxxxxx>
software developer
615-296-0838
emma(r)






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux