Joe,


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?

Fastest way should be writing a stored procedure that selects all
products and for every product looks up the desired value and sets the
description accordingly.

If you can't do that then write a small app that does this with two queries.

I think the problem is that your tables are not properly indexed for
this select (what plan do you have?)

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

That at least in one case the resulting field ( proddesc || other values
that you concatenate here) is longer than the proddesc is declared.
Example:
PRODDESC = VARCHAR(50)
Length of entry Length of appended value
30 10 ok
40 10 ok
40 11 not ok, since Result is 51
1 40 ok
1 50 not ok

and so on...

If you do it with a stored proc let the Stored proc checjk the length of
the new value before trying to apply it (or handle the error internally
in a WHEN clause)!


Luc.