Hi All,

I'm attaching below the monitor log of the execution of two queries,
that once counted generate two different kinds of error: the first is
something like "SELECT DISTINCT ... FROM", the second an almost
equivalent query using "GROUP BY" to obtain the same result.

The first seems to count every row, not just DISTINCT ones, while the
second gives a "multiple row in single select" error.

I'm using IB_WISQL 3.6D, in it's Cursor tab, hitting the SUM button.

Am I missing something or is it there something strange?

thanx,
and bye, lele.

/*---
PREPARE STATEMENT
TR_HANDLE = 16663376
STMT_HANDLE = 16667196

select distinct rdg.idfascicolo, sl.idtipologialavoro
from RapportiniDiGiornata rdg
join SchedeLavori sl ON (rdg.IDRapportinoDiGiornata = sl.IDRapportinoDiGiornata)
where sl.idtipologialavoro is not null and rdg.idfascicolo = 28

PLAN SORT (JOIN (RDG INDEX (IRG_FASCICOLODATA),SL INDEX (IPK_SCHEDELAVORI)))

FIELDS = [ Version 1 SQLd 2 SQLn 2
RAPPORTINIDIGIORNATA.IDFASCICOLO = <NIL>
SCHEDELAVORI.IDTIPOLOGIALAVORO = <NIL> ]

SECONDS = 0,030
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 16663376
STMT_HANDLE = 16667196
PARAMS = [ ]

SECONDS = 0,100
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 16663376
STMT_HANDLE = 29708188

SELECT COUNT( * )
from RapportiniDiGiornata rdg
join SchedeLavori sl ON (rdg.IDRapportinoDiGiornata = sl.IDRapportinoDiGiornata)
where sl.idtipologialavoro is not null and rdg.idfascicolo = 28
PLAN JOIN (RDG INDEX (IRG_FASCICOLODATA),SL INDEX (IPK_SCHEDELAVORI))

FIELDS = [ Version 1 SQLd 1 SQLn 1
"COUNT" = -1 ]

SECONDS = 0,040
----*/
/*---
EXECUTE2 DSQL
TR_HANDLE = 16663376
STMT_HANDLE = 29708188
PARAMS = [ ]
FIELDS = [ Version 1 SQLd 1 SQLn 1
"COUNT" = 471 ]

SELECT COUNT: 1

SECONDS = 0,070
----*/
/*---
COMMIT
TR_HANDLE = 16663376

SECONDS = 0,020
----*/
/*---
START TRANSACTION
DB HANDLE COUNT 1
TR_HANDLE = 16667484
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 16667484
STMT_HANDLE = 16663752
PARAMS = [ ]
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 16667484
STMT_HANDLE = 29708188

select rdg.idfascicolo, sl.idtipologialavoro
from RapportiniDiGiornata rdg
join SchedeLavori sl ON (rdg.IDRapportinoDiGiornata = sl.IDRapportinoDiGiornata)
where sl.idtipologialavoro is not null and rdg.idfascicolo = 28
group by rdg.idfascicolo, sl.idtipologialavoro

PLAN SORT (JOIN (RDG INDEX (IRG_FASCICOLODATA),SL INDEX (IPK_SCHEDELAVORI)))

FIELDS = [ Version 1 SQLd 2 SQLn 2
IDFASCICOLO = <NIL>
IDTIPOLOGIALAVORO = <NIL> ]

SECONDS = 0,050
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 16667484
STMT_HANDLE = 29708188
PARAMS = [ ]
----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 16667484
STMT_HANDLE = 29716340

SELECT COUNT( * )
from RapportiniDiGiornata rdg
join SchedeLavori sl ON (rdg.IDRapportinoDiGiornata = sl.IDRapportinoDiGiornata)
where sl.idtipologialavoro is not null and rdg.idfascicolo = 28
group by rdg.idfascicolo, sl.idtipologialavoro
PLAN SORT (JOIN (RDG INDEX (IRG_FASCICOLODATA),SL INDEX (IPK_SCHEDELAVORI)))

FIELDS = [ Version 1 SQLd 1 SQLn 1
"COUNT" = -1 ]

SECONDS = 0,040
----*/
/*---
EXECUTE2 DSQL
TR_HANDLE = 16667484
STMT_HANDLE = 29716340
PARAMS = [ ]
FIELDS = [ Version 1 SQLd 1 SQLn 1
"COUNT" = -1 ]

SECONDS = 0,100

ERRCODE = 335544652
----*/
/*---
INTERPRETE BUFFER =

ERRCODE = 33
----*/
/*---
INTERPRETE BUFFER = multiple rows in singleton select

ERRCODE = -1
----*/


--
nickname: Lele Gaifax | Quando vivro' di quello che ho pensato ieri
real: Emanuele Gaifas | comincero' ad aver paura di chi mi copia.
email: lele@... | -- Fortunato Depero, 1929.