Thread: EXTERN JOIN with WHEN query

EXTERN JOIN with WHEN query

From
javier garcia - CEBAS
Date:
Hi all;
This is a query that I guess is not very dificult, but I'm a newbie;
I've got a lot of tables, each of them with two columns:

SELECT * FROM precal; -> (date)     (real)  fecha    | precipitacion
------------+---------------1996-01-01 |             0.61996-02-01 |             0.7
...


But in this table there are some inexistents records (some missing days)
And I would like to create lists with a full list of dates and corresponding 
precipitation data, with gaps when the row didn't exist.
So; I've created a table with a complete series of dates from 1950 up to 
date, and made the query:
SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON 
(fechas.fecha  = precal41.fecha);

This is perfect. But to make it better,  would like to include just the dates 
from the first one in the precal table. So, I've tried:

SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON 
(fechas.fecha  = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha);
With the answer:

ERROR:  parser: parse error at or near "WHEN"

Could you help me with this query?

Thanks and regards

Javier



Re: EXTERN JOIN with WHEN query

From
"Mendola Gaetano"
Date:
I didn't read all the entire problem but try to use
WHERE instead of WHEN.

Regards
Gaetano Mendola

----- Original Message ----- 
From: "javier garcia - CEBAS" <rn001@cebas.csic.es>
To: "pgsql-sqL" <pgsql-sql@postgresql.org>
Sent: Thursday, June 05, 2003 12:55 PM
Subject: [SQL] EXTERN JOIN with WHEN query


> Hi all;
> This is a query that I guess is not very dificult, but I'm a newbie;
> I've got a lot of tables, each of them with two columns:
>
> SELECT * FROM precal; ->
>   (date)     (real)
>    fecha    | precipitacion
> ------------+---------------
>  1996-01-01 |             0.6
>  1996-02-01 |             0.7
> ...
>
>
> But in this table there are some inexistents records (some missing days)
> And I would like to create lists with a full list of dates and
corresponding
> precipitation data, with gaps when the row didn't exist.
> So; I've created a table with a complete series of dates from 1950 up to
> date, and made the query:
>
>  SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
> (fechas.fecha  = precal41.fecha);
>
> This is perfect. But to make it better,  would like to include just the
dates
> from the first one in the precal table. So, I've tried:
>
> SELECT fechas.fecha,precipitacion FROM fechas LEFT OUTER JOIN precal41 ON
> (fechas.fecha  = precal41.fecha) WHEN fechas.fecha >= min(precal41.fecha);
> With the answer:
>
> ERROR:  parser: parse error at or near "WHEN"
>
> Could you help me with this query?
>
> Thanks and regards
>
> Javier
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>