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

In message <a8274029.0309010415.78f84688@posting.google.com>, Prasad Kulkarni <prasad.kulkarni@mailcity.com> writes >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 Personally, I'd forget about the diffsign column, and when you need it use something like: select case when table1_qty1 >= table1_qty2 then 1 else -1 end from tabname then you don't need to worry about consistency between the quantities and the indicator (only what happens if either/both are null). -- Andrew Lennard andy@kontron.demon.co.uk

Thank you all for your help. I will give them a try. I am using Informix7 version. Regards Prasad Andy Lennard <andy@kontron.demon.co.uk> wrote in message news:<eBvIHAJH+0U$Ewme@kontron.demon.co.uk>... > In message <a8274029.0309010415.78f84688@posting.google.com>, Prasad > Kulkarni <prasad.kulkarni@mailcity.com> writes > >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 > > Personally, I'd forget about the diffsign column, and when you need it > use something like: > > select case > when table1_qty1 >= table1_qty2 > then 1 > else > -1 > end > from tabname> > then you don't need to worry about consistency between the quantities > and the indicator (only what happens if either/both are null).

Prasad Kulkarni wrote: > Thank you all for your help. > I will give them a try. I am using Informix7 version. 7.00, 7.1x, 7.2x, or 7.3x? Presumably, 7.31 and some bits and pieces (7.31.UD5 or something like that). You should not be using anything much earlier than that. You should be more precise when you state which version. Also, you should always state which product (you might be using IDS or SE, for example, and SE 7.2x is quite different from IDS 7.31 - and the posted solutions that don't use SPL won't work with SE); you should often state which platform - operating system and o/s version, down to SP level for Microsoft o/s. In other words, please be precise when talking about products and versions. > > Andy Lennard <andy@kontron.demon.co.uk> wrote in message news:<eBvIHAJH+0U$Ewme@kontron.demon.co.uk>... > >>In message <a8274029.0309010415.78f84688@posting.google.com>, Prasad >>Kulkarni <prasad.kulkarni@mailcity.com> writes >> >>>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 >> >>Personally, I'd forget about the diffsign column, and when you need it >>use something like: >> >>select case >> when table1_qty1 >= table1_qty2 >> then 1 >> else >> -1 >> end >> from tabname>> >>then you don't need to worry about consistency between the quantities >>and the indicator (only what happens if either/both are null). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/