Postgres Pro
Downloads
Home   >   mailing lists

Thread: Syntax for "IF" clause in SELECT

Syntax for "IF" clause in SELECT

From
pgsql@yukonho.de
Date:
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
--
email: stefan@yukonho.de
tel  : +49 (0)6232-497631
http://www.yukonho.de

Re: Syntax for "IF" clause in SELECT

From
Bricklen Anderson
Date:
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

Re: Syntax for "IF" clause in SELECT

From
Ken Hill
Date:
This has been something I've been trying do so that I can do some column comparisons as part of "data-cleaning" work.
I'lllet you know if this helps me accomplish my task!<br /><br /> On Wed, 2006-02-08 at 15:20 -0800, Bricklen Anderson
wrote:<blockquote type="CITE"><pre>
 
<font color="#000000"><a href="mailto:pgsql@yukonho.de">pgsql@yukonho.de</a> wrote:</font>
<font color="#000000">> Greetings,</font>
<font color="#000000">> </font>
<font color="#000000">> the following is an MySQL statement that I would like to</font>
<font color="#000000">> translate to PostgreSQL:</font>
<font color="#000000">> </font>
<font color="#000000">> Could someone point me to a documentation of a coresponding</font>
<font color="#000000">> Systax for an "IF" clause in the a SELECT, </font>
<font color="#000000">> or is the some other way to do this....</font>
<font color="#000000">> </font>
<font color="#000000">> select</font>
<font color="#000000">>  if(spektrum is null,' ','J'),</font>
<font color="#000000">>  if(s19 is null,' ','J'),</font>
<font color="#000000">>  if(OhneGrenze is null,' ','J'),</font>
<font color="#000000">>  from namen;</font>
<font color="#000000">> </font>
<font color="#000000">> </font>
<font color="#000000">> Do I need to create my own function to allow this behaviour!</font>
<font color="#000000">> </font>
<font color="#000000">> </font>
<font color="#000000">> my best regards,</font>
<font color="#000000">> </font>
<font color="#000000">> Stefan</font>

<font color="#000000">use CASE</font>

<font color="#000000">Since I'm not a user of MySQL, and if I'm reading your query correctly:</font>
<font color="#000000">try</font>
<font color="#000000">select (CASE when spektrum is null then 'J' else spektrum end),</font>
<font color="#000000">...</font>

<font color="#000000">or if you are just trying to replace nulls, then try COALESCE</font>

<font color="#000000">---------------------------(end of broadcast)---------------------------</font>
<font color="#000000">TIP 1: if posting/reading through Usenet, please send an appropriate</font>
<font color="#000000">       subscribe-nomail command to <a
href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a>so that your</font>
 
<font color="#000000">       message can get through to the mailing list cleanly</font>
</pre></blockquote>

Re: Syntax for "IF" clause in SELECT

From
Andrew Sullivan
Date:
What you want is the SQL-standard CASE statement.

A

On Wed, Feb 08, 2006 at 06:06:10PM -0800, Ken Hill wrote:
> 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

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
It is above all style through which power defers to reason.    --J. Robert Oppenheimer