"Evaluating" a DECODE statement? 2

[thrybergh]

Hello everyone.

I have a VIEW which returns (amongst others) a category code and a price. I would like to have a new column which returns a new category code if the price is above 1000.

e.g.

Code      Price     New Code

3103      50        3103
3103      1200      5005
3103      1400      5005
3103      6         3103

Is it possible to do this with a DECODE statement somehow? I realise that DECODE is an equality operator, but I wondered if anyone could think of a clever way to evaluate whether (price < 1000) and return a boolean or something to help me create this new column.

Thank you.

 

[Mockenrue]

Try this:

select decode ( sign ( PRICE - 1000 ) , -1 , 3103 , 0 , 3103 , 5005 ) NEW_CODE
from <your table>

 

[BJCooperIT]

How about this?

SQL> create table test_it (price number);
SQL> insert into test_it values(5.75);
SQL> insert into test_it values(1088);
SQL> insert into test_it values(0);
SQL> insert into test_it values(1000);
SQL> insert into test_it values(2678);
SQL> select price, decode(sign(1000-price),-1,'Y','N') result from test_it;

    PRICE R
--------- -
     5.75 N
     1088 Y
        0 N
     1000 N
     2678 Y

Have fun!

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[BJCooperIT]

Let me make my example a bit clearer:
[/code]
SQL> create table test_it (code varchar2(5), price number);
SQL> insert into test_it values('3103',50);
SQL> insert into test_it values('3103',1200);
SQL> insert into test_it values('3103',1400);
SQL> insert into test_it values('3103',6);
SQL> insert into test_it values('3103',0);
SQL> insert into test_it values('3103',1000);
SQL> select code, price, decode(sign(1000-price),-1,'5005',code) new
2 from test_it
3 order by code, price;

CODE PRICE NEW
----- --------- -----
3103 0 3103
3103 6 3103
3103 50 3103
3103 1000 3103
3103 1200 5005
3103 1400 5005
SQL>
[/code]

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[thrybergh]

That's fantastic BJC!

I am in debt to you. I was thinking on the lines of subtracting 1000 from the value, but I was unaware of the SIGN function until now.

Thank you again!

 

[thrybergh]

...and thanks also to Mockenrue!


:-D