Format d'un champ calculé [Résolu]

[juju05]

Bonjour,

Je cherche à formatter un champ numérique calculé à l'affichage sur SQLDEVELOPPER pour ensuite l'exporter sur Excel et faire des calculs.
Je suis sur Oracle 10g.

J'ai codé ceci:

Code :

to_char((al.MNT_TOTAL_HT * ucv.ratio_u1_u2 / (al.tot_cmde * uc.ratio_u1_u2) ),'99990,000000')

Cela me donne un résultat égal à 0.000000.

Lorsque je code:

Code :

al.MNT_TOTAL_HT * ucv.ratio_u1_u2 / (al.tot_cmde * uc.ratio_u1_u2)

je n'ai pas le même résultat.

Je souhaiterai avoir le 2ème résultat avec un formattage correct correspondant au 1er.

Merci d'avance.
Julien.

[ORA-007]

Bonjour,

http://download.oracle.com/docs/cd/B...004.htm#i34570

Que donne :

Code :

1
2
 
al.MNT_TOTAL_HT * ucv.ratio_u1_u2 / (al.tot_cmde * uc.ratio_u1_u2)

[juju05]

Bonjour,

Je ne parviens pas à formatter le champ calculé tmp.cout_unit avec 6 chiffres avant la virgule et 6 chiffres après. Je souhaite également que mon séparateur de décimales soit la virgule.

Ma requête SQL:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
SELECT tmp.id_article, tmp.reference, tmp.cout_unit AS cout_unit, tmp.num_achat
FROM 
  (SELECT a.id_article, al.MNT_TOTAL_HT / (al.NB_LOT_CMDE * al.qte_cmde * al.lg_cmde  AS cout_unit, a.reference, b.num_achat
FROM t_article a, V_RAPPRO_BL_ACHAT b, t_achat_ligne al
WHERE a.id_groupe_article IN (1,3)
  AND a.id_article = b.id_article
  AND al.id_achat_ligne = b.id_achat_ligne
  AND  a.id_unite_stock = al.id_unite_cmde                          
  AND  al.id_unite_cmde <> al.id_unite_tot_cmde  
union 
SELECT a.id_article, al.MNT_TOTAL_HT / al.tot_cmde AS cout_unit, a.reference, b.num_achat
FROM t_article a, V_RAPPRO_BL_ACHAT b, t_achat_ligne al
WHERE a.id_groupe_article IN (1,3)
  AND a.id_article = b.id_article
  AND al.id_achat_ligne = b.id_achat_ligne
  AND  a.id_unite_stock =  al.id_unite_tot_cmde
  AND al.id_unite_tot_cmde <> al.id_unite_cmde
union 
SELECT a.id_article, al.MNT_TOTAL_HT / al.tot_cmde  AS cout_unit, a.reference, b.num_achat
FROM t_article a, V_RAPPRO_BL_ACHAT b, t_achat_ligne al
WHERE a.id_groupe_article IN (1,3)
  AND a.id_article = b.id_article
  AND al.id_achat_ligne = b.id_achat_ligne
  AND  a.id_unite_stock =  al.id_unite_tot_cmde
  AND al.id_unite_tot_cmde = al.id_unite_cmde
union  
SELECT   art.id_article, a.MNT_TOTAL_HT / (a.NB_LOT_CMDE * a.qte_cmde * a.lg_cmde)  AS cout_unit, a.reference, b.num_achat
  FROM  t_article art, V_RAPPRO_BL_ACHAT b, t_achat_ligne a  
  WHERE a.id_achat_ligne = b.id_achat_ligne
   AND b.id_article   = art.id_article   
   AND    art.id_unite_stock = 1461
   AND    a.id_unite_cmde    = 1      
   AND    art.id_unite_stock <>     a.id_unite_tot_cmde
union 
SELECT   art.id_article, a.MNT_TOTAL_HT * c.ratio_u1_u2/ (a.NB_LOT_CMDE * a.qte_cmde * lg_cmde)  AS cout_unit, a.reference, b.num_achat
  FROM  t_article art, unit_conv c,V_RAPPRO_BL_ACHAT b, t_achat_ligne a
  WHERE c.id_unit_abaque = art.id_unite_abaque    
   AND  a.id_achat_ligne = b.id_achat_ligne
   AND  b.id_article     = art.id_article
   AND  c.id_unit2       = a.id_unite_cmde 
   AND  c.id_unit1       = case art.id_unite_stock 
                           when 1461 then 1
                           else 
                           art.id_unite_stock 
                           end
   AND    art.id_unite_stock <>     a.id_unite_cmde
   AND    art.id_unite_stock <>     a.id_unite_tot_cmde
union   
SELECT   art.id_article, a.MNT_TOTAL_HT * 1000 / a.tot_cmde  AS cout_unit, a.reference, b.num_achat
  FROM  t_article art, V_RAPPRO_BL_ACHAT b, t_achat_ligne a 
  WHERE a.id_achat_ligne = b.id_achat_ligne
   AND b.id_article   = art.id_article   
   AND    art.id_unite_stock = 2
   AND    a.id_unite_cmde    = 4      
   AND    a.id_unite_cmde    = a.id_unite_tot_cmde   
union 
SELECT   art.id_article, a.MNT_TOTAL_HT / (1000 * a.tot_cmde) AS cout_unit, a.reference, b.num_achat
  FROM  t_article art, V_RAPPRO_BL_ACHAT b, t_achat_ligne a  
  WHERE a.id_achat_ligne = b.id_achat_ligne
   AND b.id_article   = art.id_article   
   AND    art.id_unite_stock = 4
   AND    a.id_unite_cmde    = 2      
   AND    a.id_unite_cmde    = a.id_unite_tot_cmde
union
SELECT a.id_article,al.MNT_TOTAL_HT * ucv.ratio_u1_u2 / (al.tot_cmde * uc.ratio_u1_u2) AS cout_unit, a.reference, ach.num_achat
FROM t_article a, unit_conv uc, V_RAPPRO_BL_ACHAT b, t_achat_ligne al, unit_conv ucv, t_achat ach
 WHERE a.id_article = b.id_article
   AND a.id_unite_stock = uc.id_unit2
   AND al.id_achat_ligne = b.id_achat_ligne
   AND al.id_unite_cmde = uc.id_unit1
   AND a.id_unite_abaque = ucv.id_unit_abaque
   AND al.id_achat = ach.id_achat) tmp

Merci d'avance.
Julien.

[laurentschneider]

la virgule, c'est le séparateur international des milliers

donc

to_char('...','99990.000000')

ou si tu veux qqch de local

to_char('...','99990D000000')

avec D{ot} comme séparateur décimal