Discussion :

[david71]

Bonjour,

Quelqu'un sait-il pourquoi j'ai l'erreur suivante (ORA-1789) avec la requête suivante :

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
 
select e.num_engagement as "1",
lp.c_id_lg_prestation as "2", 
a.c_id_activite as "3",
cp.c_id_composant as "4",
i.c_id_intervention as "5",
i.l_intervention as "6", 
case t.C_TYP_TARIF when '1' then 'F'
when '2' then 'PU'
end as "7",
' ' as "8",
' ' as "9",
t.M_FORFAIT as "10",
t.M_PRIX_UNIT as "11",
' ' as "12",
' ' as "13",
substr(to_char(t.d_deb_validite,'dd/mm/yyyy'),1,10) as "14",
substr(to_char(t.d_fin_validite,'dd/mm/yyyy'),1,10) as "15"
from engagement e
inner join prestation p on p.c_id_engag = e.c_id_engag
inner join composant cp on cp.c_id_composant = p.c_id_composant
inner join activite a on a.c_id_activite = cp.c_id_activite
inner join ligne_prestation lp on lp.c_id_prestation = p.c_id_prestation
inner join intervention i on i.c_id_intervention = lp.c_id_intervention
inner join tarif t on t.c_id_lg_prestation = lp.c_id_lg_prestation
where (t.C_TYP_TARIF = '1' or t.C_TYP_TARIF = '2')
union
select e.num_engagement as "1",
lp.c_id_lg_prestation as "2", 
a.c_id_activite as "3",
cp.c_id_composant as "4",
i.c_id_intervention as "5",
'FPUTC' as "7",
' ' as "8",
'9999' as "9",
tt.m_forfait  as "10",
tt.m_prix_unit as "11",
' ' as "12",
' ' as "13",
substr(to_char(t.d_deb_validite,'dd/mm/yyyy'),1,10) as "14",
substr(to_char(t.d_fin_validite,'dd/mm/yyyy'),1,10) as "15"
from engagement e
inner join prestation p on p.c_id_engag = e.c_id_engag
inner join composant cp on cp.c_id_composant = p.c_id_composant
inner join activite a on a.c_id_activite = cp.c_id_activite
inner join ligne_prestation lp on lp.c_id_prestation = p.c_id_prestation
inner join intervention i on i.c_id_intervention = lp.c_id_intervention
inner join tarif t on t.c_id_lg_prestation = lp.c_id_lg_prestation
inner join tranche_tarif tt on tt.c_id_tarif = t.c_id_tarif
where (t.C_TYP_TARIF = '3')
and (tt.m_forfait is not null and tt.m_forfait <> 0) and (tt.m_prix_unit is not null and tt.m_prix_unit <> 0);

Merci d'avance.

[Waldar]

Vous utilisez pourtant le forum depuis longtemps, vous devriez savoir qu'un peu de recherche ne nuit pas.

Votre erreur :

ORA -1789 query block has incorrect number of result columns

Le message est très clair, il suffit d'aller lire vos deux requêtes.

Requête 1 :

Code :

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

1
2
3
4
5
6
i.c_id_intervention                    AS "5",
i.l_intervention                       AS "6", 
case t.C_TYP_TARIF
  when '1' then 'F'
  when '2' then 'PU'
end                                    AS "7",

Requête 2 :

Code :

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

1
2
i.c_id_intervention                    AS "5",
'FPUTC'                                AS "7",

Sinon vous pouvez vous passer de l'union en écrivant cette requête-ci (à tester bien sûr) :

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
select e.num_engagement                       as "1",
       lp.c_id_lg_prestation                  as "2", 
       a.c_id_activite                        as "3",
       cp.c_id_composant                      as "4",
       i.c_id_intervention                    as "5",
       i.l_intervention                       as "6", 
       case t.c_typ_tarif
         when '1' then 'F'
         when '2' then 'PU'
         when '3' then 'FPUTC'
       end                                    as "7",
       ' '                                    as "8",
       case t.c_typ_tarif
         when '3'
         then '9999'
         else ' '
       end                                    as "9",
       case t.c_typ_tarif
         when '3'
         then tt.m_forfait
         else t.m_forfait
       end                                    as "10",
       case t.c_typ_tarif
         when '3'
         then tt.m_prix_unit
         else t.m_prix_unit
       end                                    as "11",
       ' '                                    as "12",
       ' '                                    as "13",
       to_char(t.d_deb_validite,'dd/mm/yyyy') as "14",
       to_char(t.d_fin_validite,'dd/mm/yyyy') as "15"
  from engagement e
       inner join prestation p
         on p.c_id_engag         = e.c_id_engag
       inner join composant cp
         on cp.c_id_composant    = p.c_id_composant
       inner join activite a
         on a.c_id_activite      = cp.c_id_activite
       inner join ligne_prestation lp
         on lp.c_id_prestation   = p.c_id_prestation
       inner join intervention i
         on i.c_id_intervention  = lp.c_id_intervention
       inner join tarif t
         on t.c_id_lg_prestation = lp.c_id_lg_prestation
       left outer join tranche_tarif tt
         on tt.c_id_tarif        = t.c_id_tarif
        and t.c_typ_tarif        = '3'
        and tt.m_forfait is not null
        and tt.m_forfait <> 0
        and tt.m_prix_unit is not null
        and tt.m_prix_unit <> 0
 where t.c_typ_tarif in ('1', '2', '3');