Re: SQL-sentence

From: Christopher Beck <christopher.beck_at_oracle.com>
Date: Tue, 28 May 2002 15:01:20 -0400
Message-ID: <axQI8.8$2u5.210@news.oracle.com>

Jon,

given this table...

SQL> select * from foo;

     FIELD

---

         1
        -1

on oracle 8i and up you can do this...

SQL> select case

  2         when field > 0 then field - 5000
  3         else field
  4         end field

  5 from foo;

     FIELD

---

     -4999
         -1

on older version of oracle, you can do this

SQL> select decode( greatest( field, 0 ),   2 field, field-5000, field ) field   3 from foo;

     FIELD

---

     -4999
         -1

hope this helps.

chris.

--
Christopher Beck, Principal Technologist, Oracle Corporation
christopher.beck_at_oracle.com
Beginning Oracle Programming,
http://www.amazon.com/exec/obidos/ASIN/186100690X




"Jon Ole Hedne" <johedne_at_online.no> wrote in message
news:4QPI8.6628$fG3.228341_at_news2.ulv.nextra.no...


> I want to list numbers in a column in this way:

>

> If Field > 0, Field - 5000, Else Field

>

> How can I solve this with a SQL-sentence?

>

> Jon Ole Hedne

> Norway

>

>

Received on Tue May 28 2002 - 14:01:20 CDT