Hello!

Thanks for your inputs.

Even after altering the EXISTS as follows..

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

I get the same error.

There is a typo in my posting, infact there is comma as you rightly
pointed out in exists (... Table T1, Table T2)

In fact I trying to solve a Parts Explosion problem in an Inventory
i.e I am converting a Adjacency list model of table with columns for
Majpart# & Minrpart# into an appropriate Nested Set Model of Joe
Celko author of SQL for Smarties: Advanced SQL Programming (Morgan-
Kaufmann, 1999, second edition).

I am following the Stack based solution and using the modified
preorder tree traversal algorithm proposed by Joe Celko.
(www.celko.com)

Any further inputs or alternate approaches are quite welcome

Regards & Thanks

Krishna Kumar

--- In ib-support@y..., Helen Borrie <helebor@t...> wrote:
> 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/
> _______________________________________________________