pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: [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