Subject Re: [firebird-support] [Fwd: "Illegal" subquery in if-clause]
Author Helen Borrie
At 03:17 PM 21/01/2005 +0100, you wrote:

>I have a problem with a trigger.
>I wanted to do a check with a subquery in an if-clause:
>IF (NEW.ausgabe_id IN
> (SELECT ausgabe_id
> FROM sp_gedicht_ausgabenid(NEW.gedicht_id)))
>But on compiling the trigger I get an error message that the subquery is
>"illegal in this context".
>Can you tell me why the subquery - that would be much more elegant and
>would represent the case much better - is not possible?

The "illegal context" there is trying to use a context variable as an input
parameter without prefixing it with a colon - a subquery is an SQL
statement, so you can't use a stand-alone variable.

>Is it not possible at all or can I do something in a different way?

PSQL requires you to prefix variables with a colon when they are used in
SQL statements. The subquery is SQL, even though the surrounding test is PSQL.

You could try:

SELECT 1 FROM sp_gedicht_ausgabenid(:NEW.gedicht_id)
WHERE ausgabe_id = :NEW.ausgabe_id)) THEN

If that won't work, then declare local variables for the two NEW context
variables and use them instead.