Main   Products   Offshore Outsourcing   Customers   Partners   ContactUs  
JDBC Databases
  HXTT Access v7.0
  HXTT Cobol v4.0
  HXTT DBF v7.0
  HXTT Excel v6.0
 
  Buy Now
  Support
  Download
  Document
  FAQ
  HXTT Paradox v7.0
  HXTT PDF v1.1
  HXTT Text(CSV) v7.0
  HXTT Word v1.0
  HXTT XML v3.0
Offshore Outsourcing
Free Resources
  Firewall Tunneling
  Search Indexing Robot
  Conditional Compilation
  Password Recovery for MS Access
  Password Recovery for Corel Paradox
  Checksum Tool for MD5
  Character Set Converter
  Pyramid - Poker of ZYH
   
   
   
Heng Xing Tian Tai Lab of Xi'an City (abbr, HXTT)

HXTT Excel
SELECT DISTINCT wrong results
Raúl Martins
2019-04-05 07:38:21.0
Hi,

We found that SELECT DISTINCT sentence returns wrong values.
Executing with Squirrel the following sentence:


We get the following values as a result:

Location5 COUNT(*)
Biomet Deutschland 2011
Biomet France 1209
Biomet UK 1924
Changzhou Biomet Medical Devices Co., Ltd 346
Faulhaber Pinzetten OGH 112
Kapp Surgical GmbH 100
Oertel Medizintechnick 49
Zimmer GmbH 1257

Using MS Excel filter on the column "Location5" we get a different value set.
For example we found the following similar values:

Biomet Deutschland
Biomet Deutschland GmbH

Running SELECT COUNT(*) for those values we found respectively 1407 and 604 records.

Please let us know a mail address to send you the Xls file.

Thank you!!

PS: We found similar problems with XLSX files

Re:SELECT DISTINCT wrong results
HXTT Support
2019-04-05 18:05:53.0
You can send it to support@hxtt.com .

But in SQL, Biomet Deutschland != Biomet Deutschland GmbH

You can use

select "company name",count(*),LEFT("company name",RATC(' ',"company name")) as key from test.atable group by key; /* Biomet Deutschland = Biomet Deutschland GmbH*/

select "company name",count(*),LEFT("company name",RATC(' ',"company name",2)) as key from test.atable group by key; /* Biomet = Biomet Deutschland = Biomet Deutschland GmbH*/
Re:Re:SELECT DISTINCT wrong results
HXTT Support
2019-04-08 16:04:28.0
Recurred your issue:
select location5,count(*) from listmex.Hoja1 group by location5

You can add maxScanRows=0 or maxScanRows=-1 connection property, then your issue will disappear.

maxScanRows: Indicates how many rows should be scanned when determining the column types. If you set maxScanRows to 0, the entire file is scanned. If you set maxScanRows to a negative value, the file won't be scanned. Default value: 10

Search Key   Search by Last 50 Questions




Google
 

Email: webmaster@hxtt.com
Copyright © 2003-2019 Heng Xing Tian Tai Lab of Xi'an City. | All Rights Reserved. | Privacy | Legal | Sitemap