Re: DIfferential sign

On 1 Sep 2003 05:15:03 -0700, prasad.kulkarni_at_mailcity.com (Prasad Kulkarni) wrote:

>Hello All,
>
> I have a table in which there are two fields for
>quantity(table1_qty1 and table1_qty2) and one field(table1_diffsign)
>for differential sign between the two quantiies. Now i want to write a
>query to set the field table1_diffsign = 1 if table1_qty1 >=
>table1_qty2 and table1_diffsign = -1 if table1_qty1 < table1_qty2.
>
> I thought of doing
> table_diffsign = (table1_qty1 - table1_qty2) / ABS((table1_qty1 -
>table1_qty2))
>But it will give an error if table1_qty1 = table1_qty2.
>
> Does somebody know how can i achive it in a single query?
> Thanks in advance..
>
>Regards
>Prasad

Just use sign and decode
decode(sign(table1_qty1 - table1_qty2),1,1,0,1,-1)

As this formula is simply and diffsign is a computational column, you shouldn't need to store it in table1

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Sep 01 2003 - 09:21:07 CDT