|Subject||Re: [firebird-support] IN using a string from another table field|
On 12/04/12 08:15, 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 "
Your table is not normalised, you don't (shouldn't) store multiple data
items in one column. You should have a separate table consisting of the
reference IDs, one per row, for each iID. Plus, they should be numeric
and not strings!
Also, the IN clause looks for something in a *table* of values, not a
*string* of concatenated values!
For example, because the above wasn't clear:
select stuff from table
where id in (1,2,3,4,5,6);
The above is not a comm-separated list like your reference IDs are, it
is (effectively) a table with 6 rows, each with one column which is
numeric - so id is compared with 1, then 2, then .... up to 6. It is not
"substringed" as your SQL implies.
Your code is effectively this:
select stuff from table
where id in ('1,2,3,4,5,6');
Which is checking your id against a table of one row containing a column
which is a string.
Because your table in unnormalised, you need to extract the string
containing the list of referenced ids, split it up using the comma as a
delimiter, then check to see if your required ID is contained within.
It's probably easier to :
Convert your id to a string with leading and trailing commas.
Retrieve the rows required (where tSaleInvoiceCvrLtr.iID = 993).
For each row, wrap the list of ids in a leading and trailing comma.
Check if your string ",id," is contained within the retrieved string
> 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.
You are comparing a number with a string. The database engine tried to
convert the string to a number, it failed. because of the commas. Always
compare like with like.
Dunbar IT Consultants Ltd
61 Richardshaw Lane
Company Number: 05132767