[IQUG] IQ 15.2 double datatype queries not returning rows

Bhandari, Shashikant shashikant.bhandari at sap.com
Tue May 7 10:44:51 MST 2013


Double and float are approximate data types and you cannot use “equality” for those columns. Change query like
select * from dbo.query_float_double where c1 < 25.77 AND c1 > 25.75

Regards


Shashikant Bhandari
SAP Active Global Support
Shashikant.Bhandari at sap.com<mailto:Shashikant.Bhandari at sap.com>
http://www.sap.com
शशिकांत भंडारी

From: iqug-bounces at iqug.org [mailto:iqug-bounces at iqug.org] On Behalf Of Louie, David
Sent: Tuesday, May 07, 2013 1:36 PM
To: iqug at dssolutions.com
Subject: [IQUG] IQ 15.2 double datatype queries not returning rows

We have double datatype cols and cannot query these data rows out in IQ 15.2 on rows entered with larger precision.

DEVIQL.sa..51.1> sp_iqcolumn query_float_double
DEVIQL.sa..51.2> go -m vert
table_name:      query_float_double
table_owner:     dbo
column_name:     c1
domain_name:     double
width:           8
scale:           0
nulls:           N
default:         NULL
cardinality:     0
est_cardinality: 255
location:        Main
isPartitioned:   N
remarks:         NULL
check:           NULL

table_name:      query_float_double
table_owner:     dbo
column_name:     c2
domain_name:     float
width:           4
scale:           0
nulls:           N
default:         NULL
cardinality:     0
est_cardinality: 255
location:        Main
isPartitioned:   N
remarks:         NULL
check:           NULL

table_name:      query_float_double
table_owner:     dbo
column_name:     rno
domain_name:     integer
width:           4
scale:           0
nulls:           Y
default:         NULL
cardinality:     0
est_cardinality: 255
location:        Main
isPartitioned:   N
remarks:         NULL
check:           NULL

(3 rows affected, return status = 0)
DEVIQL.sa..52.1>
DEVIQL.sa..64.1> insert into dbo.query_float_double select 25.759999999999998, 25.759999999999998, 1;
(1 row affected)
DEVIQL.sa..65.1> insert into dbo.query_float_double select 25.75, 25.75, 2;
(1 row affected)
DEVIQL.sa..66.1> select * from dbo.query_float_double ;
c1                   c2                   rno
 -------------------- -------------------- -----------
            25.760000            25.760000           1
            25.750000            25.750000           2

(2 rows affected)

Tests


DEVIQL.sa..68.1> select * from dbo.query_float_double where c1 = 25.75;
c1                   c2                   rno
 -------------------- -------------------- -----------
            25.750000            25.750000           2

(1 row affected)
DEVIQL.sa..69.1>

DEVIQL.sa..69.1> select * from dbo.query_float_double where c1 = 25.76;
c1                   c2                   rno
 -------------------- -------------------- -----------

(0 rows affected)
DEVIQL.sa..69.1>

DEVIQL.sa..69.1> select * from dbo.query_float_double where c1 = 25.759999999999998;
c1                   c2                   rno
 -------------------- -------------------- -----------

(0 rows affected)
DEVIQL.sa..70.1>

In both cases the doubles with larger precision does not come back.

Thanks
David


David Louie, V.P.| AT-APG-CoreSysOps-DB Ops | BlackRock, Inc.| 212 810-3541| David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>



THIS MESSAGE AND ANY ATTACHMENTS ARE CONFIDENTIAL, PROPRIETARY, AND MAY BE PRIVILEGED. If this message was misdirected, BlackRock, Inc. and its subsidiaries, ("BlackRock") does not waive any confidentiality or privilege. If you are not the intended recipient, please notify us immediately and destroy the message without disclosing its contents to anyone. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. The views and opinions expressed in this e-mail message are the author's own and may not reflect the views and opinions of BlackRock, unless the author is authorized by BlackRock to express such views or opinions on its behalf. All email sent to or from this address is subject to electronic storage and review by BlackRock. Although BlackRock operates anti-virus programs, it does not accept responsibility for any damage whatsoever caused by viruses being passed.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20130507/2ecaae71/attachment-0001.html>


More information about the IQUG mailing list