a minor variation (not using dates, just numbers):
select
decode(
sign (table_name.numeric_column),
-1, 'NEGATIVE',
0, 'ZERO',
1, 'POSITIVE',
'drop through invalid'
) "decode_sign"
from
table_name;
---
test:
SQL> l
1 select
2 decode(
3 sign (-1),
4 -1, 'NEGATIVE',
5 0, 'ZERO',
6 1, 'POSITIVE',
7 'drop through invalid'
8 ) "decode_sign1"
9 ,
10 decode(
11 sign (0),
12 -1, 'NEGATIVE',
13 0, 'ZERO',
14 1, 'POSITIVE',
15 'drop through invalid'
16 ) "decode_sign2"
17 ,
18 decode(
19 sign (1),
20 -1, 'NEGATIVE',
21 0, 'ZERO',
22 1, 'POSITIVE',
23 'drop through invalid'
24 ) "decode_sign3"
25 from
26* dual
SQL> /
decode_s deco decode_s
-------- ---- --------
NEGATIVE ZERO POSITIVE
---end---
On 30 Oct 2000, at 13:22, krao wrote:
Date sent: Mon, 30 Oct 2000 13:22:48 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Send reply to: ORACLE-L_at_fatcity.com
From: "krao" <krao_at_callidussoftware.com>
Subject: RE: Convert SQL server case statement to Oracle
Organization: Fat City Network Services, San Diego, California
> I think it's possible to handle <, =, > etc. using decode.
>
> For example, to implement:
> If a<b then v1:= 'StrictlyLess';
> elseif a=b then v1 := 'EqualTo';
> else v1 := 'StrictlyGreater';
> do:
> v1 := decode( trunc(a/b), 0, 'StrictlyLess', decode((trunc(a/b)* round(a/b) , 1,
> 'EqualTo', 'StrictlyGreater' );
> -----Original Message-----
> I am trying to convert the following case stmt to Oracle but am stuck. I
> tried using DECODE but cannot
> handle the <= or <. Equal or Not equal I can convert by using decode.
Received on Mon Oct 30 2000 - 16:16:33 CST
