• Sign In

SQL POWER Function query

Study Forum (Inactive)

View Message

view list print
SQL POWER Function query awilson  2011-08-25 11:53
Status: Closed
 
I am using the power function in a SQL query and it will not show the full decimal points for numerics with negative exponents. I have tried CONVERT (CAST method not recognized) with FLOAT and DOUBLE and it will only show the number as 0.0

Any recommendations on expanding to show as number or scientific notation?
 
 
Matthew Bellew responded:  2011-08-25 12:11
The only way I can repro this behavior is to set an explicit format on the column. From the query designer hit "edit metadata", then select the output column and then select the "format" tab on the right side.

Make sure there is not format set or use an explicit format like

   0.###E0

http://download.oracle.com/javase/1.4.2/docs/api/java/text/DecimalFormat.html
 
Matthew Bellew responded:  2011-08-30 09:18
Sorry, I can repro this on Microsoft SQL Server. The problem seems to be that this function preserves the type of the first argument

so POWER(10,-2) = 0 (0.01 to the nearest integer)
while POWER(10.0,-2) = 0.01

The fix is to do

POWER(CAST(X AS SQL_DOUBLE), -2)