Hello,
I want to write a procedure that do the this:

from one field in the table count the number of data.

I tried this:

BEGIN

DECLARE StatCursor Cursor with return for stmt;

Unprepare stmt;


PREPARE STMT FROM 'SELECT zahl from total
                  WHERE ZAHL = ? OR ZAHL = ?
                  GROUP BY RUNDE
                  HAVING COUNT(ZAHL)>1';

OPEN STATCURSOR USING ERSTE, ZWEITE;

this work perfect, I got the whole list.

But I only want the number like COUNT and not the whole list.

Can everybody help me?
TIA
Dieter

Dieter

If all you want is the count you have to tell the puter


PREPARE STMT FROM 'SELECT COUNT(zahl) from total
WHERE ZAHL = ? OR ZAHL = ?
GROUP BY RUNDE
HAVING COUNT(ZAHL)>1';

Roy Lambert [Team Elevate]

Roy,

that was the first that I tried.....

For example I get 45 rows, but I will get 1 row with the result(45)

Dieter










Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dieter

If all you want is the count you have to tell the puter


PREPARE STMT FROM 'SELECT COUNT(zahl) from total
WHERE ZAHL = ? OR ZAHL = ?
GROUP BY RUNDE
HAVING COUNT(ZAHL)>1';

Roy Lambert [Team Elevate]

Dieter


I think what you're trying to do is impossible - its the GROUP BY clause that determines the number of rows (I'll bet you have 45 distinct RUNDE for your ZAHL = ERSTE OR ZWEITE).

If all you want is one figure you'll have to either create a temporary table and sum that or loop through your result set to get the answer.

If all you want is a single number you might be better looping through the result set.

I'd be happy to be proven wrong though.

Roy Lambert [Team Elevate]

Dieter,

<< that was the first that I tried.....

For example I get 45 rows, but I will get 1 row with the result(45) >>

If you have EDB V2, which supports derived tables (subqueries in the FROM
clause), you can use the following syntax:

prepare stmt from
'
 select count(*)
 from
 (
   select count(Zahl)
   from Total
   where Zahl = ? or Zahl = ?
   group by Runde
   having count(Zahl) > 1
 ) as t
';

Ole Willy Tuv

Ole,
thanks for your answer.

I have EDB V2.

Now I get the error: Error #700 ....Invalid expression ? found , dynamic parameter references not allowed.....

I think I must try it with a memory table.

Thanks for your answer.

Dieter












"Ole Willy Tuv" <owtuv@nospam.com> wrote:

Dieter,

<< that was the first that I tried.....

For example I get 45 rows, but I will get 1 row with the result(45) >>

If you have EDB V2, which supports derived tables (subqueries in the FROM
clause), you can use the following syntax:

prepare stmt from
'
 select count(*)
 from
 (
   select count(Zahl)
   from Total
   where Zahl = ? or Zahl = ?
   group by Runde
   having count(Zahl) > 1
 ) as t
';

Ole Willy Tuv

Roy,

yes you are right. I MUST use a tempory table.

Thanks for your answer und help.

Dieter






Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Dieter


I think what you're trying to do is impossible - its the GROUP BY clause that determines the number of rows (I'll bet you have 45 distinct RUNDE
for your ZAHL = ERSTE OR ZWEITE).

If all you want is one figure you'll have to either create a temporary table and sum that or loop through your result set to get the answer.

If all you want is a single number you might be better looping through the result set.

I'd be happy to be proven wrong though.

Roy Lambert [Team Elevate]

Ole


Even if that worked wouldn't it just give the number of rows in the subquery?

Roy Lambert

Ole,

now I have worked with CAST and it works....

Thanks once again

Dieter







Dieter Nagy <dieter.nagy@tele2.at> wrote:

Ole,
thanks for your answer.

I have EDB V2.

Now I get the error: Error #700 ....Invalid expression ? found , dynamic parameter references not allowed.....

I think I must try it with a memory table.

Thanks for your answer.

Dieter












"Ole Willy Tuv" <owtuv@nospam.com> wrote:

Dieter,

<< that was the first that I tried.....

For example I get 45 rows, but I will get 1 row with the result(45) >>

If you have EDB V2, which supports derived tables (subqueries in the FROM
clause), you can use the following syntax:

prepare stmt from
'
 select count(*)
 from
 (
   select count(Zahl)
   from Total
   where Zahl = ? or Zahl = ?
   group by Runde
   having count(Zahl) > 1
 ) as t
';

Ole Willy Tuv

Dieter


What was the final sql?

Roy Lambert