Re: Problems with views and SUM

Hi,
  I suggest that the default display width for numbers may be the apparent problem.

The variable is NUMWIDTH , i think

just set it to the width you require i.e. set numwidth 15

This will apply to all number columns not just the ones where a specific format is defined. The specific column format overrides the default numwidth variable.

Tip: to see all the defined variables in SQL*Plus do:

show all

hope this helps

graham

frivalla_at_terre.univ-lr.fr (Frederic Rivalland) wrote:

>Hi
> I ve got a problem with a SELET from a VIEW.
> I ve create a view like :
 

> create or replace view CUMUL_DEB ( exer,dates,debit) as
> select j.jou_exer,j.jou_date, sum(e.ecr_mont)
> from journal j , ecriture e
> where j.jou_ordre = e.ecr_ordre
> and j.jou_type <> 'A'
> and ecr_sens = 'D'
> group by j.jou_exer,j.jou_date;
 

> it s ok ,i ve got rigth result :
>
> SQL> desc CUMUL_DEB
> Name Null? Type
> ------------------------------- -------- ----
> EXER VARCHAR2(2)
> DATES DATE
> DEBIT NUMBER

> SQL> select * from CUMUL_DEB;
 

> EX DATES DEBIT
> -- --------- ----------
> 95 02-JAN-95 2071.07
> 95 03-JAN-95 31617635.6
> 95 04-JAN-95 11859125
> 95 05-JAN-95 18489.99
> ...
 

> My Problem is that when i do a sum with the field DEBIT i lost
>informations (decimal) :
 

> SQL> select exer, sum(debit)
> from CUMUL_DEB
> where exer = 96
> and dates < sysdate
> group by exer;
 

> EX SUM(DEBIT)
> -- ----------
> 96 291787806
>
> I will try 291787806.XX
 

>I am looking for some help, thanks.
 

>FRED.
 
>--
>___________________________________________________________________
>Frederic RIVALLAND
>Centre de Ressources Informatiques
>Universite de La Rochelle
Received on Sun Jul 07 1996 - 00:00:00 CEST