Re: SQL Question

Answers embedded

Hth,

Sybrand Bakker, Oracle DBA

Antonio Doblado <adoblado_at_icot.es> wrote in message news:88embt$fpf$1_at_talia.mad.ttd.net...
> Hello,
>
> I hace Oracle8i for Windows NT installed, and I want to develop an
> application using Visual Basic 6. Windows NT and Oracle are English
> versions, and also Visual Basic.
>
> I have to store some numerical values as varchar2 in the database, and I
> want to use the dot (".") as decimal separator, without thousand
separator.
>

Why? You are going to rely on all kind of implicit and explicit to_char and to_number conversions. This is very dangerous. Why can't you use NLS_NUMERIC_CHARACTERS for correct display (and/or NLS_TERRITORY). Just set in the registry and you will be done.

> For example a table named "TOTALS" must contain a field named
"TOTAL_PRICE"
> defined as varchar2 and values like "89.987" or "87678.5685".
>
> I want to know if it is possible to use SELECT SUM sentences using those
> varchar2 fileds and if Oracle will calculate correctly the result. For
> example, an SQL sentence like:
>
> SELECT SUM(TOTAL_PRICE) FROM TOTALS
>
> will operate correctly and add the two values mentioned before with the
next
> result: 87768.5555
>

This will probably not work and you will need an explicit conversion (select sum(to_number(total_price)) from totals.

> Thank you very much.
>
> Antonio Doblado Romo
> adoblado_at_icot.es
>
>
Received on Sun Feb 20 2000 - 15:02:05 CST