Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Basic SQL will not run |
Tue, Aug 13 2013 2:46 AM | Permanent Link |
John Tillman | 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. Attachments: Data.zip |
Tue, Aug 13 2013 4:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Aug 13 2013 4:44 AM | Permanent Link |
John Tillman | 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 |
Tue, Aug 13 2013 5:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Aug 13 2013 6:48 PM | Permanent Link |
Steve Gill | 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 |
Wed, Aug 14 2013 3:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Aug 14 2013 12:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |