For any kind of logic threatment in query I think that you can use CASE/WHEN/END method (include WHERE clausule). To make more simple your SQL, deal with null values using coalesce function, like: where (coalesce(myvalue,-1)>0).
2017-08-30 15:19 GMT-03:00 Luigi Siciliano [email protected] [firebird-support] <[email protected]>: > Hallo, > > I have a stored procedure that have two parameters: CONTO and CLIENTE > because there is two values for CONTO that needs CLIENTE parameters, all > other values of CONTO not needs CLIENTE. > > I need to have a if statement in WHERE clause to add "AND IIF > (PNC.CLIENTE_ID IS NOT NULL, PNC.CLIENTE_ID, PNC.FORNITORE_ID) = > :CLIENTE)" clause but it is not accepted, see: > > I others word the "AND IIF (PNC.CLIENTE_ID IS NOT NULL, PNC.CLIENTE_ID, > PNC.FORNITORE_ID) = :CLIENTE)" will execute only for the two values of > CONTO. > > < 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. > > -- > > Luigi Siciliano > -------------------------- > > > > ------------------------------------ > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > 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 > > > >
