X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 493459DC828 for ; Wed, 8 Feb 2006 22:05:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08106-09 for ; Wed, 8 Feb 2006 22:05:54 -0400 (AST) X-Greylist: delayed 00:12:13.703818 by SQLgrey- Received: from rwcrmhc14.comcast.net (rwcrmhc14.comcast.net [216.148.227.154]) by postgresql.org (Postfix) with ESMTP id E41A69DC813 for ; Wed, 8 Feb 2006 22:05:48 -0400 (AST) Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254]) by comcast.net (rwcrmhc14) with SMTP id <20060209020548m1400dev7pe>; Thu, 9 Feb 2006 02:05:49 +0000 Subject: Re: Syntax for "IF" clause in SELECT From: Ken Hill Reply-To: ken@scottshill.com To: Bricklen Anderson Cc: pgsql@yukonho.de, pgsql-sql@postgresql.org In-Reply-To: <43EA7CCD.3050905@presinet.com> References: <200602090012.52747.pgsql@yukonho.de> <43EA7CCD.3050905@presinet.com> Content-Type: multipart/alternative; boundary="=-4IGCqvq0Ls1jTlBwl5to" Organization: Scotts Hill Associates, Inc. Date: Wed, 08 Feb 2006 18:06:10 -0800 Message-Id: <1139450771.15713.5.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.061 required=5 tests=[AWL=0.060, HTML_MESSAGE=0.001] X-Spam-Score: 0.061 X-Spam-Level: X-Archive-Number: 200602/92 X-Sequence-Number: 24102 --=-4IGCqvq0Ls1jTlBwl5to Content-Type: text/plain Content-Transfer-Encoding: 7bit This has been something I've been trying do so that I can do some column comparisons as part of "data-cleaning" work. I'll let you know if this helps me accomplish my task! On Wed, 2006-02-08 at 15:20 -0800, Bricklen Anderson wrote: > pgsql@yukonho.de wrote: > > Greetings, > > > > the following is an MySQL statement that I would like to > > translate to PostgreSQL: > > > > Could someone point me to a documentation of a coresponding > > Systax for an "IF" clause in the a SELECT, > > or is the some other way to do this.... > > > > select > > if(spektrum is null,' ','J'), > > if(s19 is null,' ','J'), > > if(OhneGrenze is null,' ','J'), > > from namen; > > > > > > Do I need to create my own function to allow this behaviour! > > > > > > my best regards, > > > > Stefan > > use CASE > > Since I'm not a user of MySQL, and if I'm reading your query correctly: > try > select (CASE when spektrum is null then 'J' else spektrum end), > ... > > or if you are just trying to replace nulls, then try COALESCE > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly --=-4IGCqvq0Ls1jTlBwl5to Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit This has been something I've been trying do so that I can do some column comparisons as part of "data-cleaning" work. I'll let you know if this helps me accomplish my task!

On Wed, 2006-02-08 at 15:20 -0800, Bricklen Anderson wrote: 
pgsql@yukonho.de wrote:
> Greetings,
> 
> the following is an MySQL statement that I would like to
> translate to PostgreSQL:
> 
> Could someone point me to a documentation of a coresponding
> Systax for an "IF" clause in the a SELECT, 
> or is the some other way to do this....
> 
> select
>  if(spektrum is null,' ','J'),
>  if(s19 is null,' ','J'),
>  if(OhneGrenze is null,' ','J'),
>  from namen;
> 
> 
> Do I need to create my own function to allow this behaviour!
> 
> 
> my best regards,
> 
> Stefan

use CASE

Since I'm not a user of MySQL, and if I'm reading your query correctly:
try
select (CASE when spektrum is null then 'J' else spektrum end),
...

or if you are just trying to replace nulls, then try COALESCE

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly
--=-4IGCqvq0Ls1jTlBwl5to--