How can I do this better/best

From: "David" <thump@xxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 17 Mar 2005 07:00:25 -0800

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.


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

References:
- Re: demo accounts & auditing
  - From: Paul Drake

How can I do this better/best

Other related posts: