Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!tiscali!newsfeed1.ip.tiscali.net!feeder1-2.proxad.net!proxad.net!feeder1-1.proxad.net!198.186.194.251.MISMATCH!news-out.readnews.com!transit4.readnews.com!postnews.google.com!p77g2000hsh.googlegroups.com!not-for-mail
From:  IM <ian@ianmurphy.freeserve.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: oracle if else statement
Date: Wed, 06 Jun 2007 08:46:38 -0700
Organization: http://groups.google.com
Lines: 34
Message-ID: <1181144798.348054.236080@p77g2000hsh.googlegroups.com>
References: <1181123038.852859.191300@q66g2000hsg.googlegroups.com>
NNTP-Posting-Host: 198.178.192.1
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1181144798 14726 127.0.0.1 (6 Jun 2007 15:46:38 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 6 Jun 2007 15:46:38 +0000 (UTC)
In-Reply-To: <1181123038.852859.191300@q66g2000hsg.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.8.1.4) Gecko/20070515 Firefox/2.0.0.4,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: p77g2000hsh.googlegroups.com; posting-host=198.178.192.1;
   posting-account=LPaPiQ0AAAAxoPJH4IR63r6Gq6gEKL1o
Xref: news.f.de.plusline.net comp.databases.oracle.server:199027

On Jun 6, 10:43 am, odelya <be.spec...@gmail.com> wrote:
> Hi!
> I would like to write a statement using if/else for example:
> select *from table
> if value!= null then
> where value = table.value
> end if
>
> value = will be a string which might be null.
>
> im using oracle 9i

Use CASE.

SELECT <column1>, CASE
  WHEN <column1> = <condition1> THEN <result1>
  WHEN <column1> = <condition2> THEN <result2>
  WHEN NULL THEN 'BOB'
  ELSE <alternate result>
  END <identifier>

<column1> IDENTIFIER
---------------- -----------------
condition1  result1
condition2  result2
condition3  alternate result
condition4  alternate result
                 BOB

--
HTH

Ian