problem with use of distinct in sql statements |
Mike Sims |
2010-07-13 07:27:41 |
Hi I downloaded an salutation copy of the version 3 and 4 of your HXTT Text driver, however I am having problems. When ever I use the distinct command on columns of csv files which are integers, the results are not accurate.
The length of the data in this file is up to 8 numbers long. This is being brought in as a varchar in my sql client. The distinct is not accurate (many records are not being returned). I can work around the problem by casting the column as a Char() however, this is fixed length and I really need a data type that is variable length. To cover cases when this ID column is a greater length.
|
Re:problem with use of distinct in sql statements |
HXTT Support |
2010-07-13 07:52:46 |
|
Maybe you can try maxScanRows=-1 connection property. If you failed still, please let us see your sql and txt sample.
|
Re:Re:problem with use of distinct in sql statements |
Mike Sims |
2010-07-20 05:48:10 |
Yes already tried the maxScanRows with no success.
Checking the MetaData, it appears getPrecision and getColumnDisplaySize are both returning 4.
SQL below:
select distinct upper(trim("Account Id")) as appid, trim("Category") as resource from sampledata where resource = 'LBRX'
sampledata is too big to paste but I can provide over email.
Strangely, problem only occurs when using the upper and trim functions.
|
Re:Re:Re:problem with use of distinct in sql statements |
HXTT Support |
2010-07-20 23:18:21 |
>it appears getPrecision and getColumnDisplaySize are both returning 4.
>select distinct upper(trim("Account Id")) as appid, trim("Category") as resource
> from sampledata where resource = 'LBRX'
Maybe you're using an older package. Both fucntion will return 128 now. Please try with the latest package.
|
Re:Re:Re:Re:problem with use of distinct in sql statements |
Mike Sims |
2010-07-21 00:43:09 |
|
This was tested using the latest package. Same issue exists.
|
Re:Re:Re:Re:Re:problem with use of distinct in sql statements |
HXTT Support |
2010-07-21 01:14:12 |
>it appears getPrecision and getColumnDisplaySize are both returning 4.
Upload today. If it returns 4 still, please let us know your jdbc url.
|
Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements |
Mike Sims |
2010-07-21 01:57:02 |
Have downloaded and tried the very latest version. Results are the same however.
jdbc url is jdbc:text:////d:\Virtual Machines?_CSV_Header=true;MaxRowScan=-1
|
Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements |
Mike Sims |
2010-07-21 02:01:31 |
apologies. I pasted the wrong URL. correct one is:
jdbc:text:////d:\Virtual Machines?_CSV_Header=true;MaxScanRows=0
|
Re:Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements |
HXTT Support |
2010-07-21 02:03:16 |
Please send our your test sample, since we failed to recur your issue.
>select distinct upper(trim("Account Id")) as appid, trim("Category") as resource
> from sampledata where resource = 'LBRX'
It should return 128 length for upper(trim("Account Id")) .
|
Re:Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements |
Mike Sims |
2010-07-21 02:21:59 |
|
email sent to support@hxtt.com with sample file and instructions on how to recreate issue.
|
Re:Re:Re:Re:Re:Re:Re:Re:problem with use of distinct in sql statements |
HXTT Support |
2010-07-21 02:48:37 |
Found the issue.
>jdbc:text:////d:\Virtual Machines?_CSV_Header=true;MaxScanRows=0
You should use jdbc:text:////d:\Virtual Machines?_CSV_Header=true;maxScanRows=-1
with select distinct trim("Account Id") as appid from sampledata where trim(Category) = 'LBRX'
Or
jdbc:text:////d:\Virtual Machines?_CSV_Header=true;maxScanRows=0
with select distinct "Account Id" as appid from sampledata where trim(Category) = 'LBRX' since "Account Id" has been converted to int value.
We will support distinct trim(intColumn) soon.
|
Main
webmaster@hxtt.com