Re: Differential sign
Hi Prasad:
Can you use Store procedures?
update yourtable set table_diffsign = calcsign(table1_qty1, table1_qty2);
-- Define a procedure like:
create procedure calcsign(p1 int, p2 int) returning int with (not variant);define ret int;
if p1 >= p2 then
let ret = 1;
else
let ret = -1;
end if
return ret;
end procedure;
----
But you can also omit the column from the table and calcute the value each
time it's required, you can also create a functional index (what version are
using?).
select table1_qty1, table1_qty2, calcsign(table1_qty1, table1_qty2)
from yourtable
where calcsign(table1_qty1, table1_qty2) = -1;
HTH
----- Original Message -----
From: <prasad.kulkarni@mailcity.com>
To: <informix-list@iiug.org>
Sent: Monday, September 01, 2003 7:15 AM
Subject: Differential sign
> 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
sending to informix-list