I understand how it is working but I need to pass an unknown number of
elements to use inside the NOT IN. I thought passing it as a string
would do it.

should I expect it to work if I do something like

stmt = 'select count(*) from .....NOT IN (' || str || ')'
execute immediate stmt

On 20/10/06, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

> Hi Nick!
>
> > SELECT count(*) FROM TBLTXAPPMSGQ WHERE BASEIDENT NOT IN ( '1, 2, 3' )
>
> This counts all records that does not contain the 7 characters string
> '1, 2, 3' in the BASEIDENT field (most likely all records).
>
> > SELECT count(*) FROM TBLTXAPPMSGQ WHERE BASEIDENT NOT IN ( :str ) into :rcount2;
>
> Same as above.
>
> > SELECT count(*) FROM TBLTXAPPMSGQ WHERE BASEIDENT NOT IN ( 1, 2, 3 )
>
> Unlike the other two SELECTs, this one exclude all records that have the
> numbers 1 or 2 or 3 in the BASEIDENT field.
>
> Say your table contains the following rows:
>
> BASEIDENT
> 1
> 2
> 3
> 1, 2, 3
>
> If BASEIDENT was a string field, the first two selects would return 3,
> the last would probably return 4 (I think there is a difference between
> the character '1' and the integer 1. Firebird doesn't automagically turn
> one value into three, you have to split them yourself and supply three -
> not one - parameters, casting them to the correct type.
>
> HTH,
> Set
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>