Обсуждение: 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/
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/
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
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/
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/
SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_groupFROM usersUNION
WHERE username = 'test'
SELECT 0, 0
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test');
--
Mark
http://www.lambic.co.uk
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFJblF5YAVdOy4CIaIRArhBAKCLS3N+ccaigBiZKuJDOebrmbdlSwCghkYf
zFX5ktrUMPWB9BV9mg5thKo=
=g/1b
-----END PGP SIGNATURE-----
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/