Fw: EXTERN JOIN with WHEN query

----- Original Message -----
From: "Mattias Kregert" <mattias(at)kregert(dot)se>
To: "javier garcia - CEBAS" <rn001(at)cebas(dot)csic(dot)es>
Sent: Friday, June 06, 2003 10:26 AM
Subject: Re: [GENERAL] EXTERN JOIN with WHEN query

Hello Javier,

> This is a query that I guess is not very difficult, 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"

It is "WHERE", not "WHEN". I am not sure I understand what you want to do, you try to find all rows where the date in "fechas" >= the earliest date in "precal41"?

If I would have these tables, I would probably want to insert all missing dates into the "precal" tables with empty values (null) for precipitacion, so I could update them later. If this is what you want, you can do it like this:

INSERT INTO precal41(fecha,precipitation)
SELECT fecha, NULL FROM fechas
WHERE fecha NOT IN (SELECT fecha FROM precal41);
-- this will insert all the dates from fechas into precal41
-- except when the date already is in precal41
-- For the newly inserted dates, the precipitacion will be set to NULL

Then you can do:
SELECT fecha, precipitacion FROM precal41 WHERE precipitacion IS NULL;
-- This will show you all dates without precipitacion

Hope this helps :)

/Mattias