Hi Helen,

what I really wanted was

SELECT DISTINCT A,
(SELECT COUNT(*) FROM TABLE Y WHERE Y.B = X.A)
FROM TABLE X

and this gives same error, but would definitely have a distinct
solution, doesn't it ?

Best Regards
Hans

========================================

Helen Borrie wrote:

>
> At 06:45 PM 16-02-02 -0700, you wrote:
> >Reduced to simplest form, I ran into
> >a problem
> >
> >SELECT A,
> >(SELECT Y.B FROM TABLE Y WHERE Y.B = X.A)
> >FROM TABLE X
> >
> >Works fine .. but
> >
> >SELECT DISTINCT A,
> >(SELECT Y.B FROM TABLE Y WHERE Y.B = X.A)
> >FROM TABLE X
> >
> >produces following error
> >
> >ISC ERROR CODE:335544343
> >
> >ISC ERROR MESSAGE:
> >invalid request BLR at offset ..
> >context already in use (BLR error)
> >
> >STATEMENT:
> >TIB_Cursor: "frmWISQL.crEdit"
> >
> >Is this an Invalid Query or Bug ?
>
> Diane B. will be the one to pronounce on this re standards per "expected behaviour" but my first take is that it's an invalid query. A correlated subquery, by nature, must always be scalar (1:1). DISTINCT implies a non-singleton select, hence the context error.
>
> Exactly how the intermediate set for the DISTINCT is held in memory I'm not sure but I think that, in order to be able to determine a DISTINCT set involving the correlated sub-query, the engine would have to hold simultaneous access to the same subquery on Y for each occurrence of A in table X . Since DISTINCT cannot be determined until all of the candidate members for the comparison set are formed, it would have to keep recursing into a (somehow) freshly aliased subselect for each possible cor
>
> Another element that might be here is that TABLE is a single table and X and Y are aliases (which your pseudocode seems to suggest...) - re-entrant query. If this is the case, then the interdependencies/scalarity of the correlation might be even more convoluted.
>
> If you want a DISTINCT output set I think you will have to use a JOIN in order to eliminate the potential for an error to occur in the intermediate set.
>
> If not a re-entrant query:
>
> SELECT DISTINCT X.A, Y.B
> FROM X
> JOIN Y ON X.A = Y.B
>
> If it is a re-entrant query:
>
> SELECT DISTINCT X.A, Y.B
> FROM atable X
> JOIN atable Y ON X.A = Y.B
>
> (but it's still possible that the join-correlation will be absurd in the DISTINCT context if the query is re-entrant. My blood sugar is a bit low at the moment to get too focused on that!)
>
> Just some pre-Sunday-brunch thoughts, really.
>
> H.
>
> All for Open and Open for All
> Firebird Open SQL Database · http://firebirdsql.org
> _______________________________________________________
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/