How To Avoid Truncation Error In Sybase
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Why does Sybase ASE throw a truncation error in a division followed by a subtraction? up vote 3 down vote favorite I am using Sybase ASE 15. In the following SQL, there is a truncation error, but only if the division of two numbers is followed by a subtraction. declare @P1 decimal (30,16) declare @P2 decimal (30,16) select @P1 = 0.0000504412630951 --sixteen decimal places select @P2 = 0.0000512178647912 select 'This succeeds:' select @P1, @P2, (@P1 / @P2) --this succeeds: displays the values select 'This fails:' select @P1, @P2, (@P1 / @P2) - 1.0 --this fails with a truncation error. Why? select 'Why does this succeed?' select @P1, @P2, round( (@P1 / @P2), 46 ) - 1.0 --this succeeds with 46 or less, fails with 47 or greater - but why? Thanks in advance. Bob sql sybase-ase share|improve this question asked May 29 '14 at 16:51 user3041581 311 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote The 1.0 is not a decimal datatype like @P1 and @P2 , it's created as a float since you do not specify the precision and scale (or cast it as a numeric/decimal data type). The first attempt succeeds because there is no implicit conversion, you're dividing two decimals with the same precision/scale. The 2nd attempt fails because subtracting 1.0 is subtracting a float value of 1.0 against a decimal which forces an implicit conversion back to decimal. Since it is implied and not explicit, you get the truncation error because the system wants you to know
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > truncation error in sybase ? Want to Advertise Here? Solved truncation error in sybase ? Posted on 2004-11-02 Sybase Database 1 Verified Solution 4 Comments 6,318 Views Last Modified: 2010-10-05 hello experts, I get truncation error when i try to do the following set nocount on declare @var1 int, @var2 int, @var3 http://stackoverflow.com/questions/23938613/why-does-sybase-ase-throw-a-truncation-error-in-a-division-followed-by-a-subtrac numeric(10,2) select @var1 = 5 select @var2 = 6 select @var3 = @var1/@var2 select @var3 go I get a 0 in @var3 and a trucation error occured .. basically i want to do a @var1/@var2 * 100 (to find out the % ) how can this be done ? thanks 0 Question by:Sadagopank Facebook Twitter LinkedIn Google LVL 14 Best Solution byJan_Franek You need to convert @var1 to some more precise https://www.experts-exchange.com/questions/21191698/truncation-error-in-sybase.html numeric type (eg. numeric(10,2) ) to get rid of 0 and convert whole expresion to the same type to get rid of truncation error: select @var3 = convert(numeric(10,2),convert(numeric(10,2),@var1)/@var2) Go to Solution 4 Comments LVL 14 Overall: Level 14 Sybase Database 11 Message Accepted Solution by:Jan_Franek2004-11-02 You need to convert @var1 to some more precise numeric type (eg. numeric(10,2) ) to get rid of 0 and convert whole expresion to the same type to get rid of truncation error: select @var3 = convert(numeric(10,2),convert(numeric(10,2),@var1)/@var2) 0 LVL 14 Overall: Level 14 Sybase Database 11 Message Expert Comment by:Jan_Franek2004-11-02 Other way how to get rid of truncation error: set arithabort numeric_truncation off select @var3 = convert(numeric(10,2),@var1)/@var2 set arithabort numeric_truncation on 0 LVL 24 Overall: Level 24 Sybase Database 24 Message Active 5 days ago Expert Comment by:Joe Woodhouse2004-11-02 Even more simply: select @var3 = 1.0*@var1/@var2 There is a hierarchy of datatypes. When the expression is just "@var1/@var2", Sybase sees only integers, and assumes you want integer division. By adding the 1.0 at the start of the expression, Sybase understands you're talking about numerics (as numerics take precedence over integers, so the entire expression is "promoted" to a numeric). 0 Message Author Comment by:Sadagopank2004-11-02 thanks jan and joe ... the solution worked just fine. 0 Write Comment First Name Please enter a first name Last Name Please enter a last name Email We will nev
and SafetyAsset https://scn.sap.com/thread/3689716 NetworkAsset Operations and MaintenanceCommerceOverviewSubscription Billing and Revenue ManagementMaster Data Management for CommerceOmnichannel CommerceFinanceOverviewAccounting and Financial CloseCollaborative Finance OperationsEnterprise Risk and ComplianceFinancial Planning and AnalysisTreasury and Financial Risk ManagementHuman ResourcesOverviewCore Human Resources and how to PayrollHuman Capital AnalyticsTalent ManagementTime and Attendance ManagementManufacturingOverviewManufacturing NetworkManufacturing OperationsResponsive ManufacturingMarketingOverviewMarket with Speed and AgilityUnique Customer ExperiencesReal-Time Customer InsightsR&D/EngineeringOverviewDesign NetworkDesign OrchestrationProject and Portfolio ManagementSalesOverviewCollaborative Quote to CashSales Force AutomationSales Performance ManagementSelling Through Contact CentersServiceOverviewEfficient how to avoid Field Service ManagementOmnichannel Customer ServiceTransparent Service Process and OperationsSourcing and ProcurementOverviewContingent Workforce ManagementDirect ProcurementSelf-Service ProcurementServices ProcurementStrategic Sourcing and Supplier ManagementSupply ChainOverviewDemand ManagementDemand NetworkLogistics NetworkManufacturing Planning and SchedulingResponse NetworkResponse and Supply ManagementSales, Inventory, and Operations PlanningTransportation ManagementWarehouse ManagementSustainabilityOverviewEnvironment, Health, and SafetyProduct Safety and Stewardship NetworkBrowse by IndustryAerospace and DefenseAutomotiveBankingChemicalsConsumer ProductsDefense and SecurityEngineering, Construction, and OperationsHealthcareHigh TechHigher Education and ResearchIndustrial Machinery and ComponentsInsuranceLife SciencesMediaMill ProductsMiningOil and GasProfessional ServicesPublic SectorRetailSports and EntertainmentTelecommunicationsTravel and TransportationUtilitiesWholesale DistributionBrowse Platform and TechnologyAnalyticsOverviewBusiness IntelligencePredictive AnalyticsGovernance, Risk, and ComplianceEnterprise Performance ManagementApplication Platform and InfrastructureOverviewApplication PlatformContent and CollaborationInterface ManagementProcess Management and IntegrationData ManagementOverviewBig Data o