Re: Filling null values

Le vendredi 05 août 2011 à 09:32 -0700, jeffrey a écrit :
> I have a table that looks like this:
>
> homeid city date measurement pre/post
> 123 san francisco 1/2/2003 1458 pre
> 123 san francisco NULL 1932 post
> 124 los angeles 2/4/2005 938 pre
> 124 NULL NULL 266 pre
> 124 los angeles 7/4/2006 777 post
>
> I'd like to write a query so that I get the following result:
>
> homeid city date measurement pre/post
> 123 san francisco 1/2/2003 1458 pre
> 123 san francisco 1/2/2003 1932 post
> 124 los angeles 2/4/2005 938 pre
> 124 los angeles 2/4/2005 266 pre
> 124 los angeles 7/4/2006 777 post
>
> If a city or date is null, then it will fill from other not null
> values with the same homeid. If given the choice, it will
> preferentially fill from a row where homeid AND pre/post match. But
> if that doesn't match, then it will still fill from the same homeid.
>
> Does anyone have ideas for this?
>

If it's possible for you to export the data to a text file, it is very
easy to write a small Perl script that will replace NULLs by the
appropriate values.

--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique