Problème dans un 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", 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);

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

Votre erreur :

Citation:

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:

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:

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:

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');