Helen wrote:

>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.

These first two observations are not correct. You don't reuse the alias in the subselect, just refers to an outer table within the subselect, and that should be OK (if it wasn't, there would be little point in having subselects).

>-- 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.

This is probably your problem. Actually, in your tri-join, you only refer to :SelA once. Having said that, I think Jason relaxed references to the same parameter name multiple places in queries some time ago so that it - at least in some circumstances - can be reused (the relaxation was to make it possible for the same parameter to be compared to both a VARCHAR and a CHAR field, if I remember correctly). But I wouldn't trust reusing the same parameter name in both your main select and your subselect.

Just modify your query to refer to a field rather than a parameter, and my hunch is that things should work (I would typically spell out the field names rather than use csa.*, but I think it should work the way you wrote it):

select cts.CT_SPID,
(select sa.AD_SIZE from SPONSORSHIPADSIZES sa
where sa.AUCTIONID = csa.AUCTIONID
and sa.AD_SIZEID = csa.AD_SIZEID) AdSzName, csa.*
from CT_SPONSORSHIPS cts
join CT_SPONSORSHIP_ADS csa
on cts.AUCTIONID = csa.AUCTIONID
and cts.CT_SPID = csa.CT_SPID
where cts.AUCTIONID = :SelA
and cts.SPONSORSHIP_LEVELID = :SelID

HTH,
Set