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
",id,id,id,id,id,"

>
> 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.


Cheers,
Norm.


--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767