how to divide two numbers in a select ?
A newcomer reported that "select 2/1" returned 0, and that dividing real column values gave wrong results (116.66 instead of 1166.66). Replies suggested the operands' data types mattered (integer division; cast to a decimal/float type) and noted that a bare SELECT without a FROM clause is invalid — "SELECT 2/1 FROM systables WHERE tabid=1" returned 2.000000000000 for others. Since the poster was going through an ODBC DSN, he was advised to retest in dbaccess and, if that worked, use sqlidebug or onstat -g sql to see what ODBC actually sent, and otherwise contact support. Version/tool details were requested but the thread ends without a recorded resolution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I'm new to informix. I am trying to do a common division like select 2/1 and I get 0. Why? Do I have to do something special here?
maik wrote: > Hi, I'm new to informix. > I am trying to do a common division like > > select 2/1 > > and I get 0. > > Why? > Do I have to do something special here? your problem is that there is more to being a number than just having digits. I'd say you are using integers and your dividend is smaller than your divisor. Cast them to a different datatype, and you may have more luck. (And next time round, characterize your problem a bit more, before asking for help) -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
maik wrote: > Hi, I'm new to informix. > I am trying to do a common division like > > select 2/1 > > and I get 0. > > Why? > Do I have to do something special here? SELECT 2/1 FROM systables WHERE tabid = 1; gives me the answer 2.000000000000 which looks ok to me SELECT 2/1 correctly gives me a syntax error
maik wrote: > Hi, I'm new to informix. > I am trying to do a common division like > > select 2/1 > > and I get 0. > > Why? > Do I have to do something special here? SELECT 2/1 FROM systables WHERE tabid = 1; gives me the answer 2.000000000000 which looks ok to me SELECT 2/1 correctly gives me a syntax error
SELECT 2/1 FROM systables WHERE tabid = 1; gives me the answer 0 select trunc(7000 / 6) FROM systables WHERE tabid = 1; gives me the answer 1166.66 with is OK, but if I use the real data fields with those values i get 116.66 I 'm using an ODBC driver through a DSN .
what happens if you rn the same query in dbaccess?
if dbaccess gives the expected results it may be time to turn on
sqlidebug or use onstat -g sql to see what your ODBC is sending to the
server
if dbaccess is returning the wrong results it may be time to contact
your support provider
maik wrote:
> SELECT 2/1
> FROM systables
> WHERE tabid = 1;
>
> gives me the answer 0
>
> select trunc(7000 / 6)
> FROM systables
> WHERE tabid = 1;
>
> gives me the answer 1166.66 with is OK, but if I use the real data
> fields with those values
> i get 116.66
>
> I 'm using an ODBC driver through a DSN .
What version are you running and how are you submitting the select Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com GO FURTHER with DB2 GET THERE FASTER with Informix. Attend IDUG 2007 San Jose, North America May 6-10, 2006 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: maik [mailto:mistermaik@gmail.com] > Posted At: 26 October 2006 10:19 > Posted To: comp.databases.informix > Conversation: how to divide two numbers in a select ? > Subject: Re: how to divide two numbers in a select ? > > > SELECT 2/1 > FROM systables > WHERE tabid = 1; > > gives me the answer 0 > > select trunc(7000 / 6) > FROM systables > WHERE tabid = 1; > > gives me the answer 1166.66 with is OK, but if I use the real > data fields with those values i get 116.66 > > I 'm using an ODBC driver through a DSN . >
Does it think that 2/1 is a date somehow? I wonder (2+0)/(1+0) could probably give you a clue about what is going on. maik wrote: > SELECT 2/1 > FROM systables > WHERE tabid = 1; > > gives me the answer 0 > > select trunc(7000 / 6) > FROM systables > WHERE tabid = 1; > > gives me the answer 1166.66 with is OK, but if I use the real data > fields with those values > i get 116.66 > > I 'm using an ODBC driver through a DSN .