At 09:44 AM 9/03/2008, Svein Erling Tysvaer wrote:

>Hi Sergio!
>
>I tried a similar statement to yours (though simpler) in Firebird 2.1 RC1:
>
>select case (select rdb$relation_id from rdb$database)
>when 0 then 'Zero'
>when 1 then 'One'
>else 'Something else'
>end
>from rdb$database
>
>It gives a similar error to your statement.

Set, try this:

select
case rdb$relation_id
when 0 then cast('Zero' as varchar(30))
when 1 then cast('One' as varchar(30))
else cast('Something else' as varchar(30))
end
as Stringresult
from rdb$database

But your example isn't comparable with Sergio's, since there is no correlation involved in your "pseudo subquery". Sergio's case has an authentic correlated subquery. As we know so well, each result from such a subquery has a distinct and unique context. So - for each member of the "lefthand stream", we can ask the correlated question once: is <a single predicate> true or false?

Thus, for one context, the question "0 or not 0?" can be answered. We can't return to the *same* context and ask "1 or not 1?" If we try (as in Sergio's second case or your example) the best the engine can do is send its apologies = "Sorry, but I can't recycle that context because you've already used it."

Call it a bug, if you like, that Firebird doesn't have some way to materialise the output of a correlated subquery and give it reference hooks so that each of its contexts can be revisited later. It's not a criticism, since I can't think how it would be achieved. I'm more inclined to think that, if it could be done, it would have been done.

Derived tables in Fb 2 can get you around some scenarios by giving the ability to join a set to a dynamic subset in one hit. A Common Table Expression (CTE) in v.2.1 could deal with certain scenarios more effectively. I don't think DTs and CTEs can entirely supersede the entire range of things that people think they need to use correlated subqueries for, though.

There are many ways to kill a cat. The correlated subqueries are, to me, not the ideal way to get the set that Sergio wants and I wouldn't waste a lot of effort on them. A union query would normally get the same output more efficiently. Of course, if the objective is to save oneself from the tedium of typing keystrokes then it's not a winner. ;-) There might be unknown traps represented by the "dots" in Sergio's examples that make it too complex or too slow for a union query, but then we can always write selectable stored procedures...much more fun!

./heLen