Discussion :

[6boulga0]

Bonjour

voilà ma requête qui coince :

Code :

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

1
2
3
4
5
select * from (select CAEFQ.siret, CAEFQ.date_ca         
,CAEFQ.datenq ,
 last_value(round(ca)) over (partition by siret,date_ca order by decode(ca,null,to_date(null),datenq)          
 nulls   
first rows between unbounded preceding and unbounded following) ca,

/**c'est là ou ça coince
/**en fait, pour canm1, j'aimerais lui associer l'avant dernière valeur du CA
/**c'est à dire pour 2008 la valeur du CA de 2007
/**en l'occurence 21600 du tableau que je mets en pièce jointe
/**

lag(round(ca),1) over (partition by siret,date_ca order by decode(ca,null,to_date(null),datenq)
nulls first rows between
unbounded preceding and unbounded following)
canm1,

/**

last_value(eff)
over (partition by siret,date_ca order by
decode(eff,null,to_date(null),datenq) nulls first rows between
unbounded preceding and unbounded following) eff, last_value(eff)
over (partition by siret,date_ca order by
decode(eff,null,to_date(null),datenq) nulls first rows between
unbounded preceding and unbounded following) effnm1 from (select
cplmt.siret, cplmt.date_ca ,cplmt.datenq, base.CA ,base.CA_POND
,base.DATEREP ,base.EFF ,base.EFF_POND ,base.PERIM ,base.TA
,base.UNITE from CONJV200.IV_CAUNQ_E base, ((select
IV_CAUNQ_E.siret, IV_CAUNQ_E.date_ca, IV_CAUNQ_E.datenq from
CONJV200.IV_CAUNQ_E IV_CAUNQ_E, CONJV200.routunq unq where
IV_CAUNQ_E.siret=unq.siret and unq.codenq='INVT' and
unq.datenq=to_date('07/2009','mm/yyyy') and unq.ugst>='01' and
unq.ugst<='01' and (IV_CAUNQ_E.datenq<=to_date('07/2009','mm/yyyy')
and date_ca<=to_date('012008','mmyyyy'))) union (select
IV_CAUNQ_E.siret, to_date('012008','mmyyyy') date_ca,
to_date('07/2009','mm/yyyy') datenq from CONJV200.IV_CAUNQ_E
IV_CAUNQ_E, CONJV200.routunq unq where IV_CAUNQ_E.siret=unq.siret
and unq.codenq='INVT' and unq.datenq=to_date('07/2009','mm/yyyy')
and unq.ugst>='01' and unq.ugst<='01' and not exists (select
bis.siret from CONJV200.IV_CAUNQ_E bis where
(IV_CAUNQ_E.datenq=to_date('07/2009','mm/yyyy') and
date_ca=to_date('012008','mmyyyy')) and
IV_CAUNQ_E.siret=bis.siret))) cplmt where base.siret(+)=cplmt.siret
and base.date_ca(+)=cplmt.date_ca and base.datenq(+)=cplmt.datenq)
CAEFQ) where (datenq=to_date('07/2009','mm/yyyy') and
date_ca=to_date('012008','mmyyyy'))

Merci d'avance!!!