At 02:18 AM 15-05-02 +0000, you wrote:

>I have problem in using the following SQL in IBO
>
>I have a table with structure
>F1 F2 Q1
>1 1 10
>1 2 20
>1 3 40
>2 1 80
>2 2 160
>(F1,F2) is the primary key of the table
>
>and would like to get the result table with accumulated quantity Q2 of
>Q1 as below:
>F1 F2 Q1 Q2
>1 1 10 10
>1 2 20 30
>1 3 40 70
>2 1 80 80
>2 2 160 240
>
>So I use following SQL
>select F1, F2, Q1,
>(select sum(Q1) from MyTable where F1 = a.F1
>and F2 <= a.F2) as Q2
>from MyTable a
>order by F1, F2
>
>I was using with IB_SQL.exe (version 4.2Ha) in win98, Firebird 1.0 for
>testing.
>
>But I got the following errors message:
>ISC ERROR CODE:335544569
>
>ISC ERROR MESSAGE:
>Dynamic SQL Error
>SQL error code = -502
>Declared cursor already exists
>
>STATEMENT:
>TIB_Query: "<TApplication>.frmSQL.frmQuery.qrQuery."
>
>And SqL message:
>
>SQL ERROR CODE:-502
>
>SQL ERROR MESSAGE:
>The cursor identified in an OPEN statement is already open.
>
>
>
>The same SQL was acceptable when I using the other tools such as
>ibadmin. I was quite confused for what mistake I might take.

I think you were mistake, unless previously you were using an old beta of
Firebird or an IB version. Your SQL syntax is wrong and the error you see
above came from the database, not from IBO, i.e. it has nothing to do with IBO.

The reason for the dual cursor error is that your statement attempts to
"double-dip" on a single cursor by using the wrong syntax for a re-entrant
query. Here is the correct SQL:

select a.F1, a.F2, a.Q1,
(select sum(b.Q1) from MyTable b where b.F1 = a.F1
and b.F2 <= a.F2) as Q2
from MyTable a
order by a.F1, a.F2

By the way, I think you are playing with fire here by having a primary key
in your table whose individual elements are used (effectively) in
calculations. This requirement shouts out for an atomic PK, a surrogate PK
populated by a generator.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com