Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 14 total |
Count |
Sun, Jul 6 2008 10:39 AM | Permanent Link |
Dieter Nagy | 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 |
Sun, Jul 6 2008 11:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Sun, Jul 6 2008 12:00 PM | Permanent Link |
Dieter Nagy | 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] |
Mon, Jul 7 2008 2:37 AM | Permanent Link |
Roy Lambert NLH Associates 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] |
Mon, Jul 7 2008 4:41 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Mon, Jul 7 2008 5:13 AM | Permanent Link |
Dieter Nagy | 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 |
Mon, Jul 7 2008 5:16 AM | Permanent Link |
Dieter Nagy | 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] |
Mon, Jul 7 2008 5:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ole
Even if that worked wouldn't it just give the number of rows in the subquery? Roy Lambert |
Mon, Jul 7 2008 5:29 AM | Permanent Link |
Dieter Nagy | 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 |
Mon, Jul 7 2008 6:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Dieter
What was the final sql? Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |