multiplying column values in oracle SQL-CodePudding

I have following query:

SELECT RTRIM(TO_CHAR('dst.' || per_growth,
                     'FM9G999G999G999G999G990D99999999999',
                     ' NLS_NUMERIC_CHARACTERS = '',.'' '),
             ',')
  FROM dst__d_data dst;

I have concatenated per_growth column label with the dst.

Now, what I want is 'dst.' || per_growth * 0.01. However, it is rising error message:

ORA-01722: invalid number

How can I carry out multiplication in this scenario?

CodePudding user response：

Your existing query will raise the same error. You can't concatenate first, and then apply TO_CHAR; TO_CHAR expects a number, not a string. First convert the number to string, and then concatenate 'dst.' to the result of TO_CHAR.

After you fix this first mistake (even for your existing query), multiplying by 0.01 in the argument to TO_CHAR should not cause any kind of new problem.

CodePudding user response：

You can use

SELECT 'dst.'||.01 * TO_NUMBER( per_growth, 
                               'FM9G999G999G999G999G990D99999999999', 
                               'NLS_NUMERIC_CHARACTERS = '',.'' ')
  FROM dst__d_data;

prefix some string stuff after finishing the numerical conversion with '',.'' pattern(as the data seems to have comma as decimal seperator) and multiplication operation

Demo