Thread: Select CASE when null ?

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/

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

On Wed, Jan 14, 2009 at 05:35:55PM -0300, Camilo Sperberg wrote:
> 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

SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group
FROM users
WHERE username = 'test'
UNION
SELECT 0, 0
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test');


--
Mark
http://www.lambic.co.uk

On Wed, Jan 14, 2009 at 05:35:55PM -0300, Camilo Sperberg wrote:
> 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

SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group
FROM users
WHERE username = 'test'
UNION
SELECT 0, 0
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test');


--
Mark
http://www.lambic.co.uk

On Wed, Jan 14, 2009 at 17:56, Mark Styles <postgres@lambic.co.uk> wrote:

wow amazing :D that did the trick !!!! :D

I had tried COALESCE but I had not thought at all in an union...

Thanks a lot Mark for your incredible fast and fantastic response !

Greetings ;)


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

"Camilo Sperberg" <unreal4u@chilehardware.com> writes:
> SELECT
>   CASE mid WHEN NULL THEN CAST(0 AS integer)
>            ELSE mid
>   END AS mid,

BTW, the reason this doesn't work is the same reason "mid = NULL"
doesn't work, because that's exactly what the CASE condition is
treated as.  The COALESCE trick is certainly the best solution
for this specific need, but the more general way would be

    CASE WHEN mid IS NULL THEN ... ELSE ...

            regards, tom lane

On Wed, Jan 14, 2009 at 03:56:25PM -0500, Mark Styles wrote:
> SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group
> FROM users
> WHERE username = 'test'
> UNION
> SELECT 0, 0
> WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test');

An alternative using outer joins would be:

  SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group
  FROM (SELECT 1) x
    LEFT JOIN users ON username = 'test';

Unions tend to preclude various optimisations so I'd tend to stay away
from them where possible.  This query will also only perform only one
index scan of users, rather than two.

--

  Sam  http://samason.me.uk/