On Thu, 12 Apr 2012 07:15:22 -0000, "venussoftop" <[email protected]> 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
