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)