On Thu, 12 Apr 2012 07:15:22 -0000, "venussoftop" <venussoftop@...>
wrote:

> Hi all
>
> I have tried the following query
> SELECT tSaleInvoice.*
> FROM tSaleInvoice
> WHERE tSaleInvoice.iID IN
> (SELECT tSaleInvoiceCvrLtr.cSaleInvIDs
> FROM tSaleInvoiceCvrLtr
> WHERE tSaleInvoiceCvrLtr.iID = 993)
>
> The idea is to pull out records from tSaleInvoice on it's PK iID. The
> reference list of iIDs are stored as a comma separated list in a

character

> field tSaleInvoiceCvrLtr.cSaleInvID in this case - "24315,24371

> "
>
> I get the following error when I run the query and no results are

returned

> ---------------------------
> An IBPP error occurred.
> ---------------------------
> *** IBPP::SQLException ***
> Context: Statement::Fetch
> Message: isc_dsql_fetch failed.
>
> SQL Message : -413
> Overflow occurred during data type conversion.
>
> Engine Code : 335544334
> Engine Message :
> conversion error from string "24315,24371

> "

>
> I have tried to (SELECT TRIM(tSaleInvoiceCvrLtr.cSaleInvIDs)... but no
> change

No you can't. Now you have an IN list with a single value, and Firebird
attempts to convert that string value to INTEGER for comparison with iID,
this fails. You can only use IN list with explicit values list, not a
'list' in a string.

Mark