Select CASE when null ?

From: "Camilo Sperberg" <unreal4u(at)chilehardware(dot)com>
To: "postgres list" <pgsql-general(at)postgresql(dot)org>
Subject: Select CASE when null ?
Date: 2009-01-14 20:35:55
Message-ID: f05190a90901141235k1d9a38fclc24ccc0598429ecb@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list :) How are you today?

Being fast: I have the following table with the following data in it:

users:
mid --- id_group --- username
1 ----- 2 --- test
2 ----- 2 --- blabla
3 ----- 4 --- etcetc

and the following select:

SELECT
CASE mid WHEN NULL THEN CAST(0 AS integer)
ELSE mid
END AS mid,
CASE id_group WHEN NULL THEN CAST(0 AS integer)
ELSE id_group
END AS id_group
FROM users
WHERE username = 'test';

This query returns:
mid --- id_group
1 --- 2

Now, what I want is when the user isn't found, (aka WHERE username isn't
found) it should return me this:

mid --- id_group
0 --- 0

But it returns 0 rows...

I've tried so far with
CASE mid WHEN NOT FOUND -> ERROR: column "found" does not exist
CASE mid WHEN NOT EXISTS -> ERROR: column "exists" does not exist
and other variations, however, I haven't be able to accomplish this, the
idea is that it should always return at least 1 row...

I know it must be a some kind of small stupidity, but after 2 hours googling
and trying, i seriously thought of asking it to the list...

Thanks in advance ;)

Greetings, Camilo Sperberg

--
Mailed by:
UnReAl4U - unreal4u
ICQ #: 54472056
www: http://www.chw.net/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2009-01-14 20:43:59 pgdiff equiv
Previous Message Madison Kelly 2009-01-14 20:06:39 Re: Odd duplicate database