Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
AW: $COUNT unexpected behaviour
From: Zabach, Elke (elke.zabachsap.com)
Date: Tue Jul 05 2005 - 03:31:16 CDT
> MaxDB v 18.104.22.168
> Mandrake Linux 9.2
> Good morning,
> I have to create a stored proc which returns true only if recordcount>1. I
> a code based on the $COUNT "function" but it doen't work. Could someone
> tell me
> what's wrong with it ?
> CREATE DBPROC UTILISATEURS_AGENDA_ESTMULTIPLE (IN IDUTILISATEUR_AGENDA
> OUT ESTMULTIPLE BOOLEAN) AS
> SELECT 1 FROM DBA.UTILISATEURS_AGENDA
> WHERE IDUTILISATEUR_AGENDA=:IDUTILISATEUR_AGENDA;
> IF $COUNT>1 THEN ESTMULTIPLE=TRUE;
> IF $RC <> 100 THEN STOP ($RC, 'My error message');
> P.S. I tried to get the $COUNT value but it doesn't work as expected.
> P.P.S. I already found a work around in using a SELECT COUNT() query but
> like to know why the $COUNT doesn't work as expected...
In case the resultset of the <query statement> is not build physically, during select the number of resultrecords in this resultset is not known. Then §COUNT is set to -1, meaning: at least one, but an unknown number of results. (We will add it to the reference manual)
The 'workaround' is the better way of handling it as (in case the resultset would be build physically) you would build the whole resultset just to count which may cost too much performance and space.
SAP Labs Berlin
> Kind regards, Xavier.
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe: http://lists.mysql.com/maxdb?unsub=elke.zabachsap.com
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb