At 03:53 AM 11-04-02 +0000, Krishna Kumar wrote:

>Does IB 6 supports SELECT statement in the search condition of
>select_expr?

Yes: as long as the subselect returns a single column from a single
row...if the subselect can return multiple rows you will get the error
"Multiple rows in singleton select"...and a subselect won't work if it is
"select * from..."

>"Dynamic SQL Error
>SQL error code = -206
>Subselect illegal in this context"

This error description does not apply to the example you provide, as the
predicant statement here is not a subselect - it could be a mismatched
message text, for which IB is notorious...or maybe DSQL at some level is
treating the predicant statement for the EXISTS() predicate as a
subselect...if so, this is logically wrong and thus unlikely, since the
purpose of EXISTS() is to determine whether ANY rows exist, not whether a
singleton row exists (and I have never seen this error from an EXISTS()
predicate). However...

>Create Procedure Proc_1
>AS
>BEGIN
> IF (EXISTS (SELECT * FROM Table1 T1 Table2 T2
> WHERE T1.Col_m = T2.Col_n
> AND T1.Col_k = j))

This predicant statement is syntactically incorrect.
1. The SQL-89 join syntax requires the list of joined tables to be a
comma-separated list...and
2. Even if the comma separator were present, SELECT * FROM <multiple
tables> is wrong because it contains no table qualifiers. You would need
to have "SELECT T1.*, T2.* FROM Table1 T1, Table2 T2..."

Also, although it isn't syntactically wrong to select ".*" in the predicant
statement, it is actually quite daft, since EXISTS() does not return a data
set.

You could try correcting and improving the predicant statement to see
whether it removes the error, e.g.

IF (EXISTS (SELECT 1 FROM Table1 T1
join Table2 T2
ON T1.Col_m = T2.Col_n
WHERE T1.Col_k = j)) then...

However, I suspect that it is not this statement that is causing your
subselect error, but another one. I would expect the code you show here to
return an "Unknown token" error upon encountering the symbol "Table2",
because it would be expecting a comma.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________