Re: SQL query with IFs (?) to "Eliminate" NULL Values

On Wed, Sep 05, 2007 at 11:15:43AM +0200, Stefan Schwarzer wrote:
>     SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS
> y_2004, countries_view.name AS name
>     FROM pop_total, countries_view
>     LEFT JOIN tpes_total ON tpes_total.id = countries_view.id
>     WHERE pop_total.y_2004<> '0' AND pop_total.y_2004<> '-9999' AND
> tpes_total.y_2004 <> '-9999' AND countries_view.id = pop_total.id
>     ORDER BY name ASC
> So, I guess I'd need some kind of IF statement to do the calculation
> only with "valuable" numbers and pass the others as they are.
> But I have no idea how this would work.

SELECT DISTINCT
    (
        CASE
            WHEN pop_total.y_2004<> '-9999' AND tpes_total.y_2004 <> '-9999'
                then tpes_total.y_2004 / pop_total.y_2004
            ELSE '-9999'
        END
    ) AS  y_2004,
    countries_view.name AS name

should work.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)