--- In firebird-support@yahoogroups.com, Joe Martinez wrote:

> I wrote the following query for a customer of mine who wanted to
> append the product's part number (from another table) to the
> product's description. Here's what I wrote:
>
> update products A set proddesc = proddesc || ' ' || (select first 1
> vpvendpartno from vendorproducts B where B.vpupc = A.prodbarcode and
> B.vpvendor = A.prodsource)
> where (select first 1 vpvendpartno from vendorproducts B
> where B.vpupc = A.prodbarcode
> and B.vpvendor = A.prodsource) is not null
>
> Questions:
>
> 1) When I run this query, it takes FOREVER. I ended up aborting it
> after about 4 hours. The PRODUCTS table has about 32,000 records,
> and the vendorproducts table has about 192,000. Is there any way to
> modify the above query to improve efficiency so that it doesn't take
> so long?

update products A set proddesc = proddesc || ' ' || (select first 1
vpvendpartno from vendorproducts B where B.vpupc = A.prodbarcode and
B.vpvendor = A.prodsource)
where exists(select 1 from vendorproducts B
where B.vpupc = A.prodbarcode
and B.vpvendor = A.prodsource
and B.vpvendpartno is not null)

(though this assumes that there aren't records in vendorproducts for
which vpvendpartno is null for one record and something else for
another where both have the same value for vpupc and vpvendor)

> 2) When my customer tried running it, he got the following error:
>
> ISC ERROR CODE: 335544321
> arithmetic exception, numeric overflow, or string truncation
>
> What could cause that error?

Maybe Firebird got as confused as I did by your where clause, or maybe
your customer has the situation I described above with one null and
one non-null vpvendpartno for a particular vpupc and vpvendor...

HTH,
Set