Re: [firebird-support] COUNT DISTINCT issue in Fb 2.5

2017-09-05 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
> select a.num_part, a.cve_prov, a.cve_impo, a.des_merc
> from ctrac_clasif a
> where a.num_part = '0630039046';
>
>It yields this results:
>
>NUM_PART   CVE_PROV  CVE_IMPO  DES_MERC
>=  = = ==
>0630039046 3400. 19CUBIERTA
>0630039046 3400. 19CUBIERTA
>
>As you can see,  the two resulting rows are identical.

For the human eye, yes, for a computer, maybe.

>Now I run the following aggregate query:
>select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT a.des_merc)
>from ctrac_clasif a
>where a.num_part = '0630039046'
>group by a.num_part,a.cve_prov,a.cve_impo
>
>NUM_PART   CVE_PROV  CVE_IMPO  COUNT
>=  = = ==
>0630039046 3400. 192

So they are different for your computer.

Is DES_MERC defined as BLOB or something similar? If so, change to
something like:

select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT cast(a.des_merc as
varchar(32)))
from ctrac_clasif a
where a.num_part = '0630039046'
group by a.num_part,a.cve_prov,a.cve_impo

If not, do as Karol asks and show us how to replicate your case. At least,
I only get one row if I do

SELECT 'CUBIERTA' FROM RDB$DATABASE
UNION
SELECT 'CUBIERTA ' FROM RDB$DATABASE

whereas two rows are returned if I do

SELECT CAST('CUBIERTA' AS BLOB CHARACTER SET ISO8859_1) FROM RDB$DATABASE
UNION
SELECT CAST('CUBIERTA' AS BLOB CHARACTER SET ISO8859_1) FROM RDB$DATABASE

HTH,
Set

Re: [firebird-support] COUNT DISTINCT issue in Fb 2.5

2017-09-04 Thread 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
Hi,

will be better to show sample script
create table
and insert into..
and db charset
then anyone can test this self

regards,
Karol Bieniaszewski

From: eadbha...@yahoo.com.mx [firebird-support] 
Sent: Monday, September 4, 2017 7:34 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] COUNT DISTINCT issue in Fb 2.5

  

Hi there,




I have this query:




select a.num_part, a.cve_prov, a.cve_impo, a.des_merc

from ctrac_clasif a


where a.num_part = '0630039046';



It yields this results:



NUM_PART CVE_PROV CVE_IMPO DES_MERC

= = = ==

0630039046 3400. 19 CUBIERTA

0630039046 3400. 19 CUBIERTA




As you can see,  the two resulting rows are identical. 




Now I run the following aggregate query:




select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT a.des_merc)

from ctrac_clasif a

where a.num_part = '0630039046'

group by a.num_part,a.cve_prov,a.cve_impo


NUM_PART CVE_PROV CVE_IMPO COUNT

= = = ==

0630039046 3400. 19 2




Shouldn't COUNT be equals to 1? There's only one distinct value in the DES_MERC 
column.




Your help is much appreciated.




Regards,




Eduardo





---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

[firebird-support] COUNT DISTINCT issue in Fb 2.5

2017-09-04 Thread eadbha...@yahoo.com.mx [firebird-support] 
Hi there,
 

 I have this query:
 

 select a.num_part, a.cve_prov, a.cve_impo, a.des_merc
 from ctrac_clasif a
 
 where a.num_part = '0630039046';
 

 It yields this results:
 

 NUM_PART CVE_PROV CVE_IMPO DES_MERC
 = = = ==
 0630039046 3400. 19 CUBIERTA
 0630039046 3400. 19 CUBIERTA
 

 As you can see, the two resulting rows are identical. 
 

 Now I run the following aggregate query:
 

 select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT a.des_merc)
 from ctrac_clasif a
 where a.num_part = '0630039046'
 group by a.num_part,a.cve_prov,a.cve_impo
 

 NUM_PART CVE_PROV CVE_IMPO COUNT
 = = = ==
 0630039046 3400. 19 2
 

 Shouldn't COUNT be equals to 1? There's only one distinct value in the 
DES_MERC column.
 

 Your help is much appreciated.
 

 Regards,
 

 Eduardo