How can I do this better/best

From: David <thump_at_cosmiccooler.org>
Date: Thu, 17 Mar 2005 07:00:25 -0800
Message-ID: <004c01c52b02$11747160$d8912540@winston>

SELECT
x.*,
CASE
    WHEN c = 0 THEN lpad(x||'.0', 5, ' ')     WHEN c > 0 THEN lpad(x, 5, ' ')
    ELSE NULL
END || '%' output_var
FROM (

     SELECT
     99.9 x,
     instr( 99.9, '.', 1, 1) c
     from dual

) x;

Takes in 1 argument (here I have it hardcoded as 99.9, but it would need to be a bind variable) Outputs a varchar2 (output_var)

I couldn't figure it out.

Here is what it does:

         X C OUTPUT_VAR

---------- ---------- ----------------
         1          0   1.0%
        11          0  11.0%
       100          0 100.0%
      15.9          3  15.9%
       8.4          2   8.4%

So I can pass in any number and output a properly formatted number with 1 decimal place and a percent sign.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 17 2005 - 10:04:13 CST