this is the sql

UPDATE Product
SET Product.ProductGroup = ProductDetail.Program3
FROM Product INNER JOIN ProductDetail ON ProductDetail.ProductCode = Product.ProductCode;
WHERE PG_description = 'Unassigned';

UPDATE Product
SET Product.StockUnit = StockUnit.StockUnit
FROM Product INNER JOIN StockUnit ON StockUnit.ProductGroup = Product.ProductGroup
WHERE SU_Description = 'Unit';

Error as *.gif and data attached in zip. This copy of the data shows the fields concerned.The live clients data is the one I need to modify.

John


A suggestion to start with about etiquette on these newsgroup - if you have a large chunk of data to post please post it to the binaries not the standard newsgroup.

Secondly it would have saved me having to download and examine your tables in DBSys if you'd posted the structure rather than the tables.

Finally it would again have made things easier if you'd posted the error rather than a jpg.


Having looked at things the problem seems to be Product.ProductGroup is an integer and ProductDetail.Program3 is a string so you're trying to assign a string to an integer. What you'll need to do is CAST the string field to an integer, making sure its not null first eg

UPDATE Product
SET Product.ProductGroup = CAST(COALESCE(TRIM(BOTH ' ',  ProductDetail.Program3),'0') AS INTEGER)
FROM Product INNER JOIN ProductDetail ON ProductDetail.ProductCode = Product.ProductCode
WHERE description = 'Unassigned';

Roy Lambert

Roy Lambert wrote:

John


A suggestion to start with about etiquette on these newsgroup - if you have a large chunk of data to post please post it to the binaries not the standard newsgroup.

Secondly it would have saved me having to download and examine your tables in DBSys if you'd posted the structure rather than the tables.

Finally it would again have made things easier if you'd posted the error rather than a jpg.


Having looked at things the problem seems to be Product.ProductGroup is an integer and ProductDetail.Program3 is a string so you're trying to assign a string to an integer. What you'll need to do is CAST the string field to an integer, making sure its not null first eg

UPDATE Product
SET Product.ProductGroup = CAST(COALESCE(TRIM(BOTH ' ',  ProductDetail.Program3),'0') AS INTEGER)
FROM Product INNER JOIN ProductDetail ON ProductDetail.ProductCode = Product.ProductCode
WHERE description = 'Unassigned';

Roy Lambert

Thank you Roy That seems to run in my test data. I will bear in mind your etiquette instructions next time.
Did not see how to get the error as text hence the picture and I should have just sent the 2 tables that were needed.
Apologies from a newbee.
John Tillman

John


>Thank you Roy That seems to run in my test data. I will bear in mind your etiquette instructions next time.

All those with slow connections will thank you

>Did not see how to get the error as text hence the picture and I should have just sent the 2 tables that were needed.

That was one I only leant a short while ago, I think Tim told me or I may have found out by accident.

Click onto the error message and press ctrl-c then you can paste it just like anything else on the clipboard.

I find it especially useful when I've built a piece of SQL in code and its not working - add a showmessage, ctrl-c, ctrl-v and I have something to test in DBSys or EDBManager.

>Apologies from a newbee.

No need - we all have to start somewhere and learn as we go along.

Roy Lambert [Team Elevate]

Hi Roy,

<< Click onto the error message and press ctrl-c then you can paste it just like anything else on the clipboard. >>

What the?  That's so cool!  I've have been coding Windows apps since 3.11 and I never knew you could do that.

I showed some developers and testers here and they didn't know about it either.  We always use screenshots of error messages.

Thanks Roy.

-Steve

Steve

>What the? That's so cool! I've have been coding Windows apps since 3.11 and I never knew you could do that.

That is pretty much what my reaction was - it seems to be a well kept secret

Roy

Roy,

<< That was one I only leant a short while ago, I think Tim told me or I may
have found out by accident. >>

I might have told you, but I only found it out in the last couple of years
myself.  Unfortunately, I can't for the life of me remember who it was that
told me about it, or where I read it.

Tim Young
Elevate Software
www.elevatesoft.com