> < snip>
> from
>
> PN_TESTA PNT
> JOIN PN_CORPO PNC on PNT.ID = PNC.PN_TESTA_ID
> WHERE
> PNC.CONTO_ID = :CONTO
>
> if :CLIENTE is not null then <-- *IS NOT ACCEPTED*
>
> AND IIF (PNC.CLIENTE_ID IS NOT NULL, PNC.CLIENTE_ID,
> PNC.FORNITORE_ID) = :CLIENTE)
>
> <snip>
>
> There's a solution?
>
> Thanks.
>
Of course you cannot treat a SQL statement as if it was any programming
language, your IF makes it impossible for the stored procedure to know
what the SQL statement will look like at compilation time! EXECUTE
STATEMENT was introduced to allow dynamic building of SQL statements,
but you shouldn't do that in your case - in my opinion it would be
considerably better to just modify your statement slightly to use a CTE:
with tmp( MyCliente ) as /*turn the parameter into a CTE*/
( select cast( :CLIENTE as integer ) /*or varchar or whatever*/
from rdb$database )
SELECT <snip>
FROM PN_TESTA PNT
JOIN PN_CORPO PNC on PNT.ID = PNC.PN_TESTA_ID
CROSS JOIN TMP T /*make sure this is the last tuple in your query to
keep the choices for the optimizer*/
WHERE PNC.CONTO_ID = :CONTO
AND ( T.MyCliente IS NULL /* i.e. if :CLIENTE is NULL */
OR T.MyCliente = COALESCE( PNC.CLIENTE_ID, PNC.FORNITORE_ID ) )
/*this coalesce is equivalent to your 'iif', just a bit simpler*/
HTH,
Set
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
