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 concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat('<LIGNE><DONNEE>',c1),'</DONNEE><DONNEE>'), c2),'</DONNEE><DONNEE>'),rtrim(char(integer(sum(c3))))),'</DONNEE><DONNEE>'), max(c4)),'</DONEE><DONNEE>'),char(decimal(sum(c5),10,2))),'<DONNEE><DONNEE>'),rtrim(char(integer(sum(c6))))),'</DONNEE><DONNEE>'),max(c7)),'</DONNEE><DONNEE>'),char(decimal(sum(c8),10,2))),'</DONNEE><DONNEE>'),max(c9)),'</DONNEE></LIGNE>')
from
((
select R.roufou6 as c1, R.roufou2 as c2, count(D.numdocument) as c3, cast(max(D.heucreation) as varchar(26)) as c4, sum(D.PRIXTOTALHT) as c5, 0 as c6, '2
000-01-01 00:00:00.001' as c7, 0 as c8, substr(max(R.roufou4),1,16) as c9
from MEMI_DF M, PFAC_DOCUMENT D, MROU_ROUTAGE R
where
( ( M.codctrfour = 'FR0000018160' and M.codcptfour ='003' and M.documentid = R.documentid and date(M.DATECREATION) = '2005-12-22' )
or ( M.codctrfour = 'FR0000018160' and M.codcptfour ='003' and M.documentid = R.documentid
and ( M.documentid in ( select O.objetid from LYNX_OBJETSTATUT O, LYNX_ETAPEOBJSTATUT E where
O.statutid = E.statutid
and E.nomactionoperation ='POSEARA_POSEARA'
and (date(E.DATECREATION) = '2005-12-22' ) ) ) )
)
and
( D.codctrfour = 'FR0000018160' and D.codcptfour ='003' and D.documentid = R.documentid
and substr(R.roufou6,11,6) = '200510'
)
and
( D.codctrfour = 'FR0000018160' and D.codcptfour ='003' and D.documentid = R.documentid and date(D.HEUCREATION) <= '2005-12-22'
)
group by R.roufou6,R.roufou2
)
union all
(
select R.roufou6 as c1, R.roufou2 as c2, 0 as c3, '2000-01-01' as c4, 0 as c5, count(D.numdocument) as c6, cast(max(D.heucreation) as varchar(26)) as c7,
sum(D.PRIXTOTALHT) as c8, substr(max(R.roufou4),1,16) as c9
from MEMI_DF M, PFAC_DOCUMENT D, MROU_ROUTAGE R
where
( ( M.codctrfour = 'FR0000018160' and M.codcptfour ='003' and M.documentid = R.documentid and date(M.DATECREATION) = '2005-12-22' )
or ( M.codctrfour = 'FR0000018160' and M.codcptfour ='003' and M.documentid = R.documentid
and ( M.documentid in ( select O.objetid from LYNX_OBJETSTATUT O, LYNX_ETAPEOBJSTATUT E where
O.statutid = E.statutid
and E.nomactionoperation ='POSEARA_POSEARA'
and (date(E.DATECREATION) = '2005-12-22' ) ) ) )
)
and
( D.codctrfour = 'FR0000018160' and D.codcptfour ='003' and D.documentid = R.documentid
and substr(R.roufou6,11,6) = '200510'
)
and
(
( D.codctrfour = 'FR0000018160' and D.codcptfour ='003' and D.documentid = R.documentid
and ( D.documentid in ( select O.objetid from LYNX_OBJETSTATUT O ,LYNX_ETAPEOBJSTATUT E where
O.statutid = E.statutid
and E.nomactionoperation ='POSEARA_POSEARA'
and (date(E.DATECREATION) <= '2005-12-22' ) ) ) )
)
group by R.roufou6,R.roufou2
)) as gnop
group by c1, c2 |
Partager