Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Need Help with / operator (possible bug)

4 posts in ,  Connectivity - ODBC Windows NT Last posting was on 1998-05-11 10:10:35.0Z
Luis Cordeiro Posted on 1998-05-08 16:46:17.0Z
From: "Luis Cordeiro" <luis_cordeiro@mail.amsinc.com>
Subject: Need Help with / operator (possible bug)
Date: Fri, 8 May 1998 17:46:17 +0100
Lines: 39
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <#po0Mepe9GA.196@forums.powersoft.com>
Newsgroups: sybase.public.connectivity.odbc,sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.connectivity.odbc:2512 sybase.public.sqlserver.nt:173
Article PK: 115470

I am running the following query in ODBC, but running it in a ISQL window
does not help also:


select intValue1 / intValue2
from table
where intValue1 / intValue2 <= 0.1


then I tried the following ( in ISQL):


select convert(real,intValue1 / intValue2)
from table
where convert(real,intValue1 / intValue2) <= 0.1



...what I am trying to find out is a set of values where the percentage
between 2 columns (intValue1 and intValue2), is less or equal then 10%
(0,1).
Nevertheless I am receiving back all the values from the table (even those
where this proportion is bigger), and the result column is always equal to 0
(zero)...this is the reason why I am receiving all the columns, since zero
makes the where condition true.


...intValue1 and intValue2 are integers different than zero.


Server Version:

SQL Server/11.0.3.2/P/Digital UNIX/3.2/SWR 7568 Rollup/OPT/Mon Nov 3
23:43:31 PST 1997


Thanks


Luis Cordeiro Posted on 1998-05-08 17:29:38.0Z
From: "Luis Cordeiro" <luis_cordeiro@mail.amsinc.com>
References: <#po0Mepe9GA.196@forums.powersoft.com>
Subject: Re: Need Help with / operator (possible bug)
Date: Fri, 8 May 1998 18:29:38 +0100
Lines: 57
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <A0Rgd2pe9GA.154@forums.powersoft.com>
Newsgroups: sybase.public.connectivity.odbc,sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.connectivity.odbc:2510 sybase.public.sqlserver.nt:172
Article PK: 115465

I converted the intValues before the division to float and it seams to work,
but my problem is that the ODBC driver its not putting the convert
statements automatically.
My question is, should Sybase SQL Server, since this is an expression
column, return the appropriate datatype even if the operands are integers ?
Should the ODBC driver put the convert statements ? (the result query it's
still wrong if I run the query through ODBC)

Luis Cordeiro wrote in message <#po0Mepe9GA.196@forums.powersoft.com>...
>I am running the following query in ODBC, but running it in a ISQL window
>does not help also:
>
>
>select intValue1 / intValue2
>from table
>where intValue1 / intValue2 <= 0.1
>
>
>then I tried the following ( in ISQL):
>
>
>select convert(real,intValue1 / intValue2)
>from table
>where convert(real,intValue1 / intValue2) <= 0.1
>
>
>
>...what I am trying to find out is a set of values where the percentage
>between 2 columns (intValue1 and intValue2), is less or equal then 10%
>(0,1).
>Nevertheless I am receiving back all the values from the table (even those
>where this proportion is bigger), and the result column is always equal to
0
>(zero)...this is the reason why I am receiving all the columns, since zero
>makes the where condition true.
>
>
>...intValue1 and intValue2 are integers different than zero.
>
>
>Server Version:
>
>SQL Server/11.0.3.2/P/Digital UNIX/3.2/SWR 7568 Rollup/OPT/Mon Nov 3
>23:43:31 PST 1997
>
>
>Thanks
>
>


Bret Halford Posted on 1998-05-08 18:30:41.0Z
Message-ID: <35534F51.3D51@sybase.com>
Date: Fri, 08 May 1998 12:30:41 -0600
From: Bret Halford <bret@sybase.com>
Organization: Customer Service & Support
X-Mailer: Mozilla 3.0 (X11; U; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Luis Cordeiro <luis_cordeiro@mail.amsinc.com>
Subject: Re: Need Help with / operator (possible bug)
References: <#po0Mepe9GA.196@forums.powersoft.com> <A0Rgd2pe9GA.154@forums.powersoft.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.connectivity.odbc,sybase.public.sqlserver.nt
Lines: 69
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.connectivity.odbc:2509 sybase.public.sqlserver.nt:171
Article PK: 115463

the expression "intValue1 / intValue2" is going to do an
integer division regardless of whether it is wrapped in
a convert() or not. The appropriate way to write
your query would be:

select convert(real,intValue1) / convert(real, intValue2) as "ratio"
from table
where convert(real,intValue1) / convert(real, intValue2) < = 0.1

(although I would prefer numerics to real)

You probably should review the "datatype hierarchy" rules described
in the Transact-SQL User's Guide. The appropriate result type for a
division of two ints is an int, regardless of whether you later convert
that value to
a real or not.

There is a distinct difference between doing an int division and
converting
the result to a real and doing a real division. ODBC cannot
second-guess
what you want, so it does (or should do, anyway) exactly what you
specify.

Are the ODBC results incorrect when you run the query above?

-bret

Luis Cordeiro wrote:
>
> I converted the intValues before the division to float and it seams to work,
> but my problem is that the ODBC driver its not putting the convert
> statements automatically.
> My question is, should Sybase SQL Server, since this is an expression
> column, return the appropriate datatype even if the operands are integers ?
> Should the ODBC driver put the convert statements ? (the result query it's
> still wrong if I run the query through ODBC)
>
> Luis Cordeiro wrote in message <#po0Mepe9GA.196@forums.powersoft.com>...
> >I am running the following query in ODBC, but running it in a ISQL window
> >does not help also:
> >
> >
> >select intValue1 / intValue2
> >from table
> >where intValue1 / intValue2 <= 0.1
> >
> >
> >then I tried the following ( in ISQL):
> >
> >
> >select convert(real,intValue1 / intValue2)
> >from table
> >where convert(real,intValue1 / intValue2) <= 0.1
> >
> >
> >
> >...what I am trying to find out is a set of values where the percentage
> >between 2 columns (intValue1 and intValue2), is less or equal then 10%
> >(0,1).
> >Nevertheless I am receiving back all the values from the table (even those
> >where this proportion is bigger), and the result column is always equal to
> 0
> >(zero)...this is the reason why I am receiving all the columns, since zero
> >makes the where condition true.
> >
> >
> >...intValue1 and intValue2 are integers different than zero.
> >
> >
> >Server Version:
> >
> >SQL Server/11.0.3.2/P/Digital UNIX/3.2/SWR 7568 Rollup/OPT/Mon Nov 3
> >23:43:31 PST 1997
> >
> >
> >Thanks
> >
> >

--
Bret Halford Imagine my disappointment
Sybase Technical Support in learning the true nature
3665 Discovery Drive of rec.humor.oracle...
Boulder, CO 80303


Luis Cordeiro Posted on 1998-05-11 10:10:35.0Z
From: "Luis Cordeiro" <luis_cordeiro@mail.amsinc.com>
References: <#po0Mepe9GA.196@forums.powersoft.com> <A0Rgd2pe9GA.154@forums.powersoft.com> <35534F51.3D51@sybase.com>
Subject: Re: Need Help with / operator (possible bug)
Date: Mon, 11 May 1998 11:10:35 +0100
Lines: 10
X-Newsreader: Microsoft Outlook Express 4.72.2106.4
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4
Message-ID: <CCt$3uLf9GA.155@forums.powersoft.com>
Newsgroups: sybase.public.connectivity.odbc,sybase.public.sqlserver.nt
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.connectivity.odbc:2508 sybase.public.sqlserver.nt:170
Article PK: 115464

If I run this query in MSAccess throught ODBC, the SQL that's sent to the
server does not include the converts !
I will try to use the cdbl function to see if this works