18329

Integer and not DECIMAL

Question:

Why I am getting Integer in this output (I am expecting DECIMAL) Database flavor is Teradata What am I doing wrong?

CASE WHEN sub_e.LSD IS NOT NULL THEN CAST(baba.amt AS DECIMAL(10,2)) ELSE 0 END - ( SUM( CASE WHEN sub_e.LSD IS NOT NULL THEN ch.actv_amt * (1 + ch.tax_percent_rate/100) ELSE 0 END ) ) + CASE WHEN sub_e.LSD IS NOT NULL THEN COALESCE(sub_e.PWC, 0) ELSE 0 END AS ODTS,

Answer1:

If TAX_RATE_PERCENT is not defined as a DECIMAL of <strong><em>sufficient precision</em></strong> then your calculation of (1 + TAX_RATE_PERCENT/100) will be incorrect.

SELECT 6/100; -- 0 (zero - Integer division)

SELECT 6.0/100; -- 0.1 (rounding rules based on precision of numerator)

SELECT 6.00/100; -- 0.06 (expected answer)

SELECT CAST(6 AS DECIMAL(2,1))/100; -- 0.1 (rounding rules based on precision of numerator)

SELECT CAST(6 AS DECIMAL(3,2))/100; -- 0.06 (expected answer)

For more details on DECIMAL data type behavior in Teradata see Dnoeth's explanation <a href="https://stackoverflow.com/questions/17574015/decimal-types-in-teradata" rel="nofollow">here</a>.

Answer2:

the problem was in the definition of a solumn inside the table where the result of the query above is doing INSERT

Recommend

  • App crashing after selecting photo from gallery
  • Best way to read/parse a untyped binary file in Delphi
  • Get Wages Yearly Increment Column Wise Using Sql
  • Two Datatables in One Page
  • How to integrate dynamically JS code in success page in magento
  • Pygame trigonometry: Following the hypotenuse?
  • Need clear idea about timespec structure
  • Spark dataframes: Extract a column based on the value of another column
  • find the value of pi till 50 digits
  • The binary operator LessThan is not defined for the types 'System.Nullable`1[System.DateTime]&#
  • Changing NULL's position in sorting
  • float vs double comparison [duplicate]
  • Json max int number
  • Globalized custom number formatting - Variable decimal points
  • How do I conditionally select a field from one of two tables?
  • Will new Date().toJSON() always be unique in Javascript?
  • WebGL: Access buffer from shader
  • Default CUDA addition rounding mode between cuda 5.0 and 7.5
  • Can I plot times when they're given as UNIX timestamps with added milliseconds?
  • Java 8/Javascript (Nashorn) long interoperatiblity
  • How do I use cURL & PHP to spoof the referrer?
  • How can I fill all space of a div 100% with 3 divs with the same size?
  • SSIS package hangs while running
  • How does CoreLocation locate the device?
  • 403 forbidden error while sending messages to facebook connector through Unification Engine API
  • Proper way to add unescaped text from a field to a regex in postgres?
  • Serverless Framework Dynamo DB Table Resource Definition with Sort Key
  • How are 32 bit JavaScript numbers resulting from a bit-wise operation converted back to 64 bit numbe
  • Fraction length
  • Jooq casting String to BigDecimal
  • Flex/AS3 very strange simple Number operation issue
  • HALF_PTR Windows data type
  • How Get arguments value using inline assembly in C without Glibc?
  • Servlet stops working on Tomcat server after some hits or time
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • How to detect interior vertices in groups of 2d polygons? (E.g. ZIP Codes to determine a territory)
  • Approximate Order-Preserving Huffman Code
  • Grails calculated field in SQL
  • Is possible to count alias result on mysql
  • How to get Windows thread pool to call class member function?