Arithmetic Comparison in Select Clause

[thermidor]

Hi All:

I would like to know how to accomplish an arithmetic comparison in a select clause. I am joining two tables. I need to compare two values that exist at the join and say if value A is =< value B then String A else String B, and I need to do this in my Select clause.

Can anyone explain how to do this?

TIA,
Sven

 

[BJCooperIT]

In your WHERE clause you may use:

decode(sign(B - A),-1,B,A)

Example:

SQL> create table test_bc (a number, b number);

Table created

SQL> insert into test_bc values(1,3);

1 row inserted

SQL> insert into test_bc values(5,5);

1 row inserted

SQL> insert into test_bc values(10,7);

1 row inserted

SQL> select decode(sign(B - A),-1,B,A) from test_bc;

DECODE(SIGN(B-A),-1,B,A)
------------------------
                       1
                       5
                       7

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus