Discussion :

[hadid]

Bonjour,

je reçois ce message d'erreur : ORA-00937:not a single-group group function lors de d'un select. Comment corriger ce select suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
select rtax.tax_cod t_cod,
       tax_amount tax_amount,
       tax_dsc tdsc,
       tax_lin t_lin,
       bud.bud_cod,
       bud_dsc,
       trunc((cme.cash_mean_amount * tax_amount) / cdi.cdi_amount_total)  v_montant,
       sum(trunc((cme.cash_mean_amount * tax_amount) / cdi.cdi_amount_total))  v_somme,
      HADI.v_max,  
      HADI.v_reste,
       'in'status,
       cme.cash_mean_cod mea_cod
from   csh_gen_cdi cdi, rcp_tax rtax, untaxtab tax, unbudtab bud,
		(select  RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD,
                               max(nvl(CASH_MEAN_REFNBER,'$')) CASH_MEAN_REFNBER,
                               max(nvl(CASH_BANK_COD,'$')) CASH_BANK_COD, sum(CASH_MEAN_AMOUNT) CASH_MEAN_AMOUNT
                     from  csh_mea  
                     group by RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD) cme,
           (select   cm.cash_mean_cod, cm.rcp_year, cm.rcp_cuo, cm.rcp_serial, cm.rcp_nber, 
                     max(trunc((cm.cash_mean_amount * rtx.tax_amount) / cdii.cdi_amount_total)) as v_max,
                     cm.cash_mean_amount - sum(trunc((cm.cash_mean_amount * rtx.tax_amount) / cdii.cdi_amount_total)) as v_reste
        from   csh_gen_cdi cdii, rcp_tax rtx, untaxtab tt, unbudtab bt,
                  (select  RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD,
                               max(nvl(CASH_MEAN_REFNBER,'$')) CASH_MEAN_REFNBER,
                               max(nvl(CASH_BANK_COD,'$')) CASH_BANK_COD, sum(CASH_MEAN_AMOUNT) CASH_MEAN_AMOUNT
                     from  csh_mea  
                     group by RCP_YEAR, RCP_CUO, RCP_SERIAL, RCP_NBER, CASH_MEAN_COD) cm
        where
      	cdii.ope_dat      =  '20060905'   and
       	cdii.rcp_cuo      =  'CMDLP'     and
       	rtx.rcp_serial  =  cdii.rcp_serial and
       	rtx.rcp_nber    =  cdii.rcp_nber   and
       	rtx.rcp_year    =  cdii.rcp_year   and
       	rtx.rcp_cuo     =  cdii.rcp_cuo    and
       	rtx.tax_cod     =  tt.tax_cod    and
       	tt.bud_cod      =  bt.bud_cod    and
           	tt.lst_ope     !=  'D' and
            cm.rcp_serial   =  rtx.rcp_serial and
       	cm.rcp_nber     =  rtx.rcp_nber   and
       	cm.rcp_year     =  rtx.rcp_year   and
      	cm.rcp_cuo      =  rtx.rcp_cuo  
          group by  cm.cash_mean_cod, cm.rcp_year, cm.rcp_cuo, cm.rcp_serial, cm.rcp_nber, cm.cash_mean_amount) HADI 
where
       cdi.ope_dat      =  '20060905'and
       cdi.rcp_cuo      =  'CMDLP'     and
       rtax.rcp_serial  =  cdi.rcp_serial and
       rtax.rcp_nber    =  cdi.rcp_nber   and
       rtax.rcp_year    =  cdi.rcp_year   and
       rtax.rcp_cuo     =  cdi.rcp_cuo    and
       rtax.tax_cod     =  tax.tax_cod    and
       bud.bud_cod      =  tax.bud_cod    and
       tax.lst_ope     !=  'D'		and
       cme.rcp_serial   =  rtax.rcp_serial and
       cme.rcp_nber     =  rtax.rcp_nber   and
       cme.rcp_year     =  rtax.rcp_year   and
       cme.rcp_cuo      =  rtax.rcp_cuo	and
       HADI.rcp_year     =  cdi.rcp_year   and
       HADI.rcp_cuo      =  cdi.rcp_cuo    and
       HADI.rcp_serial   =  cdi.rcp_serial and
       HADI.rcp_nber     =  cdi.rcp_nber   and
       HADI.cash_mean_cod = cme.cash_mean_cod     
order by 5, 4, 1

Je veux necessairement obtenir la sum et le trunc Est-ce possible ?

[nuke_y]

Ajoute ceci juste avant le order by :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
group by
rtax.tax_cod,
       tax_amount,
       tax_dsc,
       tax_lin,
       bud.bud_cod,
       bud_dsc,
       trunc((cme.cash_mean_amount * tax_amount) / cdi.cdi_amount_total),
      HADI.v_max,  
      HADI.v_reste,
       'in'status,
       cme.cash_mean_cod

[hadid]

Ajoute ceci juste avant le order by :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
group by
rtax.tax_cod,
       tax_amount,
       tax_dsc,
       tax_lin,
       bud.bud_cod,
       bud_dsc,
       trunc((cme.cash_mean_amount * tax_amount) / cdi.cdi_amount_total),
      HADI.v_max,  
      HADI.v_reste,
       'in'status,
       cme.cash_mean_cod

Je vais essayer et je vous dirais si c'est bon demain.
Merci

[solo_naina]

bonjour hadid,
comme je vois tu travaille sur Asycuda++, je suis confronté à un problème et je me demandais si tu pouvais m'aider :
#1|12|csh_gen_cdi|6|V6|203|
$NBERR:1|
Merci d'avance.