At 06:41 AM 18/10/2012, lcampbell wrote:

Well, I won't even think about trying to simulate your situation or explain why your bad SQL might "work" in some environments but not in others. That's the only bit that's "strange" to me. The DB engine should throw at least one exception when preparing this query. Try fixing the query or stick with the tri-join.

There are three important mistakes in the subquery.

-- the attempted re-use of the same cursor alias (csa) for table CT_SPONSORSHIP_ADS in both the main query and the subquery. They need to be separated. I suspect that's the main source of your crash.

-- the HIGHLY not recommended practice of using SQL-89 syntax for inner joins, especially in complex queries, as you have done in the subquery. While it's still "valid" in the SQL engine for legacy purposes, it can be a problem for the API, especially one such as IBO which does a lot of acrobatics with the WHERE clause. If, for some abstruse reason you must resort to SQL-89 join syntax, IBO needs you to *tell* it which parts of the WHERE clause are JoinLinks.

-- multiple uses of the same parameter identifier (:SelA), under the apparent misapprehension that SQL parameters are variables. They're not. In this case, that second hit on :SelA is probably a candidate for an AV that crashes the connection.

select
cts.CT_SPID,
(select sa.AD_SIZE from SPONSORSHIPADSIZES sa
JOIN CT_SPONSORSHIP_ADS csa1 /* second cursor */
ON csa1.AD_SIZEID = sa.AD_SIZEID
where sa.AUCTIONID = :SelA) AdSzName,
csa.* /* first cursor */
from
CT_SPONSORSHIPS cts
join
CT_SPONSORSHIP_ADS csa
on
cts.AUCTIONID = csa.AUCTIONID and
cts.CT_SPID = csa.CT_SPID
where
cts.AUCTIONID = :SeIA1 and
cts.SPONSORSHIP_LEVELID = :SelID

However (sans testing) the second hit on that parameter should be unnecessary, anyway. I can't see an immediate reason why the correlated sa.AUCTIONID would be unavailable as the selector for the main query, making your main WHERE clause
where
cts.AUCTIONID = sa.AUCTIONID and
cts.SPONSORSHIP_LEVELID = :SelID

Test it and see whether it gets the result you want.

A look at firebird.log should give some clue as to what *actually* prompted the crash.

NB. When posting problem SQL to lists, avoid filling it up with extraneous non-syntactic characters. It's a turn-off to anyone who might be interested in helping you with the problem.

HTH,
Helen