Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
Truncation errors |
Tue, Dec 22 2009 10:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
This is going to be part of an import routine from Excel spreadsheets. I think you have an error in how you handle parameters exception class : EEDBError exception message : ElevateDB Error #1011 An error occurred with the value Technical Architect (The value would result in truncation). This nice simple bit of SQL SELECT _JobCode FROM JobCodes WHERE _JobCode = :Value OR _JobDesc = :Value was replaced with SELECT _JobCode FROM JobCodes WHERE (LENGTH(:Value) <= LENGTH(_JobCode) AND _JobCode = SUBSTR(:Value,1,LENGTH(_JobCode))) OR (LENGTH(:Value) <= LENGTH(_JobDesc) AND _JobDesc = SUBSTR(:Value,1,LENGTH(_JobDesc))) which also doesn't work when :Value is Senior Director, Worldwide Strategic Planning & Business Intelligence if I alter it to SUBSTR(:Value,1,10) I still get the error and also if I use (LENGTH(:Value) <= LENGTH(_JobDesc) AND _JobDesc = CAST(:Value AS VARCHAR(10))) CREATE TABLE "JobCodes" ( "_JobCode" VARCHAR(10) COLLATE "ANSI_CI", "_JobDesc" VARCHAR(35) COLLATE "ANSI_CI", CONSTRAINT "PK" PRIMARY KEY ("_JobCode") ) Roy Lambert |
Tue, Dec 22 2009 11:49 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< This is going to be part of an import routine from Excel spreadsheets. I think you have an error in how you handle parameters exception class : EEDBError exception message : ElevateDB Error #1011 An error occurred with the value Technical Architect (The value would result in truncation). >> I'll make sure this is fixed for the next build, but for now you're going to have to make sure that any parameter values are within the sizes of the columns being compared against. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 22 2009 1:28 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>I'll make sure this is fixed for the next build, but for now you're going to >have to make sure that any parameter values are within the sizes of the >columns being compared against. Whew - I had this nightmare that you were going to say it was part of the standard I've already programmed round it, might even be more effective since I'm potentially replacing a query with a length test but code has risen from c10 lines to c100. Roy Lambert |
Mon, Dec 28 2009 10:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
><< This is going to be part of an import routine from Excel spreadsheets. I >think you have an error in how you handle parameters > > exception class : EEDBError > exception message : ElevateDB Error #1011 An error occurred with the value >Technical Architect (The value would result in truncation). >> > >I'll make sure this is fixed for the next build, but for now you're going to >have to make sure that any parameter values are within the sizes of the >columns being compared against. Even that doesn't work. I get ElevateDB Error #1011 An error occurred with the value An (The value would result in truncation) with this sql SELECT _ID, _Forename FROM Contacts WHERE _Surname = :Surname AND (_Forename LIKE :Forename+'%' OR _Forename IS NULL OR :Forename IS NULL) AND ( _Mobile = :Mobile OR _HomePhone = :HomePhone OR _HomeEMail = :HomeEMail ) An is the :Forename parameter. _Forename is defined as VARCHAR(15) This (SUBSTR(_Forename,1,2) = :Forename OR _Forename IS NULL OR :Forename IS NULL) seems to work Roy Lambert |
Tue, Dec 29 2009 9:39 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Even that doesn't work. I get ElevateDB Error #1011 An error occurred with the value An (The value would result in truncation) with this sql >> It's due to the fact that you're concatenating the parameter with '%', which is confusing the automatic type/length inference for the parameter. Assign the '%' as part of the parameter, and it will work. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 29 2009 11:24 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>It's due to the fact that you're concatenating the parameter with '%', which >is confusing the automatic type/length inference for the parameter. Assign >the '%' as part of the parameter, and it will work. If its able to be confused does that mean you've won the AI prize Roy Lambert |
Wed, Dec 30 2009 10:32 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< If its able to be confused does that mean you've won the AI prize >> I wish - I could then take a vacation and let the computer do all of the work. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |