Bonjour, voici 1 requête qui est censée afficher les informations ouvrières et patronales sur 1 même ligne (en fonction des taux). Mais je souhaiterais n'afficher que les lignes qui n'ont pas BASE_OUVR,MNT_OUVR , BASE_PATR et MNT_PATR valant 0. Oracle me jette quand je veux les référencer directement. vace par exemple:

ORA-00904: "MNT_PATR" : identificateur non valide.

Voici ma requête:

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 distinct 'IRCANTEC'                REGIME_RETRAITE,
       nat.idf_agent                          MATRICULE,
       his.nom_usuel || ' ' || his.nom_prenom NOM_PRENOM,  
       sum(bul.bas_rub)    BASE_OUVR ,   
       bul.tau_rub 	   TAU_OUVR  ,          
       sum(bul.mnt_rub)    MNT_OUVR  ,
       (select sum(bul2.bas_rub) from rh.bulpai_anter bul2 , 
                                                 rh.vue_charges vue
         where bul2.idf_agent = nat.idf_agent                                      
         and bul2.cod_rub = vue.cod_rub
         and vue.typ_cotis ='IRC'
         and ind_cotis ='P'
         and bul2.cod_rub not like '84%R'
         and ( ( bul2.tau_rub + bul.tau_rub = 5.63 ) or 
	    ( bul2.tau_rub + bul.tau_rub = 17.5)     )
         group by substr(bul2.cod_rub,1,3)
                   )        BASE_PATR  ,        
        (select distinct bul2.tau_rub from rh.bulpai_anter bul2                                                     ,rh.vue_charges vue 
         where bul2.idf_agent = nat.idf_agent 
         and bul2.cod_rub = vue.cod_rub 
         and vue.typ_cotis ='IRC'
         and ind_cotis ='P'
         and bul2.tau_rub <> 0
         and ( ( bul2.tau_rub + bul.tau_rub = 5.63 ) or 
                 ( bul2.tau_rub + bul.tau_rub = 17.5)     )
                   )      TAU_PATR  ,          
       (select sum(bul2.mnt_rub) from rh.bulpai_anter bul2,rh.vue_charges vue
        where bul2.idf_agent = nat.idf_agent
        and bul2.cod_rub = vue.cod_rub  
        and vue.typ_cotis ='IRC'                                      
        and ind_cotis ='P'
        and ( ( bul2.tau_rub + bul.tau_rub = 5.63)  or 
                ( bul2.tau_rub + bul.tau_rub = 17.5)  or 
     ( (bul2.tau_rub + bul.tau_rub = 2.25) and (bul2.cod_rub ='841R') ) or 
     ( (bul2.tau_rub + bul.tau_rub = 5.95) and (bul2.cod_rub ='843 R') )    )               
 
       group by substr(bul2.cod_rub,1,3)
       )        MNT_PATR 
FROM og.ident_his his,
     rh.agtnat_anter nat ,
     rh.agtpai_anter pai ,
     rh.bulpai_anter bul,
     rh.vue_charges vue
WHERE   nat.ident_id   = his.ident_id
and     pai.idf_agent = nat.idf_agent
and     pai.num_emploi = 1
and	pai.num_periode = 1
and     pai.cod_coll ='INRIA'
and     nat.cod_coll ='INRIA'
and    	pai.cod_regret <>4
and 	pai.idf_agent = bul.idf_agent
and 	pai.idf_cle = bul.idf_cle
and 	pai.cod_regcot not in (61,62,63,64,65,66,67,68)
AND   	nat.cod_coll ='INRIA'
AND   	to_date(to_char(his.dat_debut,'DD/MM/YYYY'),'DD/MM/YYYY') <= to_date(to_char(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY')
AND   	to_date(to_char(his.dat_fin,'DD/MM/YYYY'),'DD/MM/YYYY')   > to_date(to_char(sysdate,'DD/MM/YYYY'),'DD/MM/YYYY')
and 	bul.cod_rub = vue.cod_rub
and 	vue.typ_cotis ='IRC'                                      
and 	ind_cotis ='O' 
group by nat.idf_agent,his.nom_usuel ||' '|| his.nom_prenom,substr(bul.cod_rub,1,3),bul.tau_rub 
ORDER BY nat.idf_agent,bul.tau_rub;