pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] CASE WHEN / 6.4.2? + no doc in installed documentation
3+ messages / 3 participants
[nested] [flat]
* Re: [SQL] CASE WHEN / 6.4.2? + no doc in installed documentation
@ 1999-05-26 14:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Emils Klotins <emils@mail.usis.bkc.lv>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 1999-05-26 14:07 UTC (permalink / raw)
To: emils@mail.usis.bkc.lv; +Cc: pgsql-sql
"Emils Klotins" <emils@mail.usis.bkc.lv> writes:
> Could somebody please tell what is the correct form for either IFNULL
> or CASE SQL functions in PG?
Looking in the grammar I find:
/* Case clause
* Define SQL92-style case clause.
* Allow all four forms described in the standard:
* - Full specification
* CASE WHEN a = b THEN c ... ELSE d END
* - Implicit argument
* CASE a WHEN b THEN c ... ELSE d END
* - Conditional NULL
* NULLIF(x,y)
* same as CASE WHEN x = y THEN NULL ELSE x END
* - Conditional substitution from list, use first non-null argument
* COALESCE(a,b,...)
* same as CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ... END
* - thomas 1998-11-09
*/
I think these things will actually be documented in 6.5 :-).
BTW, there are some severe bugs in CASE in 6.4.*; it doesn't work
reliably in queries that reference more than one table. Fixed in 6.5,
as far as I know.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* CASE WHEN / 6.4.2? + no doc in installed documentation
@ 1999-05-26 15:50 Emils Klotins <emils@mail.usis.bkc.lv>
0 siblings, 1 reply; 3+ messages in thread
From: Emils Klotins @ 1999-05-26 15:50 UTC (permalink / raw)
To: pgsql-sql
Hello.
Could somebody please tell what is the correct form for either IFNULL or CASE SQL functions
in PG?
I have a SELECT that needs to return a value, if another value is not null, otherwise it should
return a string 'N/A':
A simpliefied version of SELECT is this:
SELECT category, CASE (WHEN category IS NULL THEN 'N/A' ELSE name) AS name FROM
mytable;
I get 'parse error near category'.
What would be the correct form or alternate way to do this?
Thanks in advance for any help!!
NB. SQL functions are NOT mentioned at all in the PG documentation which comes with the
6.4.2 version in SuSE 6.1 Linux. I only found it in the "User's guide", section 6 of the PG
documentation on Postgresql.org web site.
------------------------------------------------
Emils.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Message of the day:
Ambition is a poor excuse for not having enough sense to be lazy.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
RSA PGP key published on: http://pgpkeys.mit.edu:11371
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] CASE WHEN / 6.4.2? + no doc in installed documentation
@ 1999-05-26 20:57 Fomichev Michael <fomichev@null.ru>
0 siblings, 0 replies; 3+ messages in thread
From: Fomichev Michael @ 1999-05-26 20:57 UTC (permalink / raw)
To: Emils Klotins <emils@mail.usis.bkc.lv>; +Cc: pgsql-sql
On Wed, 26 May 1999, Emils Klotins wrote:
>
> I have a SELECT that needs to return a value, if another value is not null, otherwise it should
> return a string 'N/A':
> A simpliefied version of SELECT is this:
>
> SELECT category, CASE (WHEN category IS NULL THEN 'N/A' ELSE name) AS name FROM
> mytable;
>
> I get 'parse error near category'.
>
Try this:
SELECT category, name FROM mytable WHERE category IS NOT NULL
UNION ALL
SELECT category, 'N/A' FROM mytable WHERE category IS NULL;
field `name' must have char type.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-05-26 20:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-26 15:50 CASE WHEN / 6.4.2? + no doc in installed documentation Emils Klotins <emils@mail.usis.bkc.lv>
1999-05-26 14:07 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-26 20:57 Re: [SQL] CASE WHEN / 6.4.2? + no doc in installed documentation Fomichev Michael <fomichev@null.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox