displaying extra characters with COLUMN-defined number format?

[Alex]

Here's a tricky SQL question that has definitely driven me to the end of
my rope. I'm using Oracle 9i and I need to perform some simple
multiplication on a field and then display it with a percent sign using
the COLUMN command. Here's the code thus far:

COLUMN price format 9,999.99 HEADING 'Charged%'
SELECT pricecharged * .231 as price
FROM VT_examdetail

The output from this reads:

Charged%
---------
23.10
34.65
34.65
....


The kicker here is that I need to add a percent sign to the right of the
output, so that it reads:


Charged%
---------
23.10%
34.65%
34.65%
....

I thought I could do this by just adding "|| ('%')" into the SELECT
statement, but when I do this the decimal position defined in the COLUMN
command is lost. Does anyone know another way around this?

Thanks,
Alex

[Lyndon Hills]

Re: displaying extra characters with COLUMN-defined number format?

On Tue, 07 Oct 2003 08:58:56 -0700, Alex <raindogs_1@yah oo.com> wrote:
[color=blue]
>Here's a tricky SQL question that has definitely driven me to the end of
>my rope. I'm using Oracle 9i and I need to perform some simple
>multiplicati on on a field and then display it with a percent sign using
>the COLUMN command. Here's the code thus far:
>
>COLUMN price format 9,999.99 HEADING 'Charged%'
>SELECT pricecharged * .231 as price
>FROM VT_examdetail
>
>The output from this reads:
>
> Charged%
>---------
> 23.10
> 34.65
> 34.65
>...
>
>
>The kicker here is that I need to add a percent sign to the right of the
>output, so that it reads:
>
>
> Charged%
>---------
> 23.10%
> 34.65%
> 34.65%
>...
>
>I thought I could do this by just adding "|| ('%')" into the SELECT
>statement, but when I do this the decimal position defined in the COLUMN
>command is lost. Does anyone know another way around this?
>
>Thanks,
>Alex
>[/color]
TO_CHAR(number, '99.99') || '%'

[Manoj Rajshekar]

Re: displaying extra characters with COLUMN-defined number format?

Hi,

You could do this:

select cast( charged as varchar(10)) + '%' from orders

Regards,
-Manoj

[Greg D. Moore \(Strider\)]

Re: displaying extra characters with COLUMN-defined number format?


"Alex" <raindogs_1@yah oo.com> wrote in message
news:3F82E2C0.4 030204@yahoo.co m...[color=blue]
> Here's a tricky SQL question that has definitely driven me to the end of
> my rope. I'm using Oracle 9i and I need to perform some simple
> multiplication on a field and then display it with a percent sign using
> the COLUMN command. Here's the code thus far:[/color]

Couple of comments, this is a ms-sqlserver forum, not Oracle.

However, the other answers should work.

But, I'd say you're going about this the wrong way. Formatting should be
done at an entirely different level than the DB.

[color=blue]
>
> COLUMN price format 9,999.99 HEADING 'Charged%'
> SELECT pricecharged * .231 as price
> FROM VT_examdetail
>
> The output from this reads:
>
> Charged%
> ---------
> 23.10
> 34.65
> 34.65
> ...
>
>
> The kicker here is that I need to add a percent sign to the right of the
> output, so that it reads:
>
>
> Charged%
> ---------
> 23.10%
> 34.65%
> 34.65%
> ...
>
> I thought I could do this by just adding "|| ('%')" into the SELECT
> statement, but when I do this the decimal position defined in the COLUMN
> command is lost. Does anyone know another way around this?
>
> Thanks,
> Alex
>
>[/color]