How to SUBSTR a calculated row in SQL Query?

Original Query:

SELECT F4105.COUNCS/10000 FROM F4105

Output:

 Numeric Expression 
  --------------------
  111.1643000000000000 
  111.1633000000000000 
  111.1633000000000000 
  101.7654000000000000 
  101.7654000000000000 
  112.7258000000000000

I need to remove at least the last 5 zeroes. I tried to do a substring but it didn't work.

Here is the query(s) i tried:

(1) 
   SELECT SUBSTR((F4105.COUNCS/10000 AS 'co'),length((co)-5) FROM F4105  
(2)
   SELECT SUBSTR((F4105.COUNCS/10000),length((F4105.COUNCS/10000)-5)) FROM F4105

The 1st query gave me and error:

Token F4105 was not valid. Valid tokens: (.

The 2nd query worked by wrong output.

Solution

You are mixing the column alias definition in the expression. So, the correct expression is more like:

SELECT SUBSTR(F4105.COUNCS/10000, length(F4105.COUNCS/10000.0) - 5) as coFROM F4105

I wouldn't recommend doing this, however. You have a numeric expression. Just convert it to a decimal representation that you want, say:

SELECT CAST(F4105.COUNCS/10000.0 as DECIMAL(10, 5))