Thread: CASE WHEN / 6.4.2? + no doc in installed documentation
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
"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