ven. 28 mai 2021, 15h51
Damonzon
Mali
atoutfox.public.association
Re: Report avec une table parent et plusieurs tables enfants (4 ou 5)
Bonjour,
J'ai fait le test, tous les curseurs intermédiaires ramènent avec la colonne nbre avec 1 record. Seul le curseur final, à savoir : bulletin ramène des nombres effarants de records. On compte souvent plus de 100 de fois le même numéro matricule.
Ci-après la requête.
Bon après midi.
Lc_societe = societe.societe
Lc_periode = societe.datepaie
SELECT bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullent.salbase,bullent.salnet,bullent.salbrut,;
bullent.typsal,bullent.pnet,bullent.sitfam,bullent.conv,bullent.categ,bullent.classific,bullent.dept,bullent.serv,bullent.fonction,;
bullent.nbrenf,bullent.datentre,bullent.nbrpart,bullent.numsecu,bullent.modepaie,bullent.nomjfille,bullent.nom,bullent.prenoms, ;
bullent.nbull,bullent.freq,bulldet.societe as detsoc,bulldet.periode as detper,bulldet.matricule as matdet,;
bulldet.clef as clefdet,bulldet.coderub,bulldet.librub,;
SUM(bulldet.montant) as montdet,SUM(bulldet.nbrebase) as nbredet,SUM(bulldet.taux) as tauxdet,SUM(bulldet.tauxits) as itsdet,;
SUM(bulldet.tauxret) as retdet,bulldet.cmotif, COUNT(*) as nbrecords_ent;
FROM bullent ;
INNER Join bulldet On bullent.entid=bulldet.detid AND bullent.societe=bulldet.societe AND bullent.matricule=bulldet.matricule AND;
bullent.clef=bulldet.clef AND bullent.periode=bulldet.periode;
GROUP BY bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullent.salbase,bullent.salnet,bullent.salbrut,;
bullent.typsal,bullent.pnet,bullent.sitfam,bullent.conv,bullent.categ,bullent.classific,bullent.dept,bullent.serv,bullent.fonction,;
bullent.nbrenf,bullent.datentre,bullent.nbrpart,bullent.numsecu,bullent.modepaie,bullent.nomjfille,bullent.nom,bullent.prenoms, ;
bullent.nbull,bullent.freq,bulldet.societe,bulldet.periode,bulldet.matricule,bulldet.clef,bulldet.coderub,bulldet.librub,;
bulldet.montant,bulldet.nbrebase,bulldet.taux,bulldet.tauxits,bulldet.tauxret,bulldet.cmotif ;
WHERE bullent.societe=Lc_societe AND bullent.periode=Lc_periode AND !DELETED("bullent");
INTO CURSOR cursdet
SELECT bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullcharg.societe as chargsoc,bullcharg.periode as chargper,bullcharg.matricule as matcharg,;
bullcharg.clef as clefcharg,bullcharg.chargid,bullcharg.coderub as rubcharg,bullcharg.librub as libcharg,SUM(bullcharg.montant) as montcharg,;
SUM(bullcharg.nbrebase) as nbrecharg,SUM(bullcharg.taux) as tauxcharg,bullcharg.codcharg, COUNT(*) as nbrecords_charg;
FROM bullent ;
INNER Join bullcharg On bullent.entid=bullcharg.chargid AND bullent.societe=bullcharg.societe AND bullent.matricule=bullcharg.matricule AND;
bullent.clef=bullcharg.clef AND bullent.periode=bullcharg.periode;
GROUP BY bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullcharg.societe,bullcharg.periode,bullcharg.matricule,;
bullcharg.clef,bullcharg.chargid,bullcharg.coderub,bullcharg.librub,bullcharg.montant,bullcharg.nbrebase,;
bullcharg.taux,bullcharg.codcharg;
WHERE bullent.societe=Lc_societe AND bullent.periode=Lc_periode AND !DELETED("bullent");
INTO CURSOR curscharg
SELECT bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullnet.societe as netsoc,bullnet.periode as netper,bullnet.matricule as matnet,;
bullnet.clef as clefnet,bullnet.netid,bullnet.coderub as rubnet,bullnet.librub as libnet,SUM(bullnet.montant) as montnet,SUM(bullnet.nbrebase) as nbrenet,;
SUM(bullnet.taux) as tauxnet, COUNT(*) as nbrecords_net;
FROM bullent ;
INNER Join bullnet On bullent.entid=bullnet.netid AND bullent.societe=bullnet.societe AND bullent.matricule=bullnet.matricule AND;
bullent.clef=bullnet.clef AND bullent.periode=bullnet.periode;
GROUP BY bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullnet.societe,bullnet.periode,bullnet.matricule,;
bullnet.clef,bullnet.netid,bullnet.coderub,bullnet.librub,bullnet.montant,bullnet.nbrebase,bullnet.taux ;
WHERE bullent.societe=Lc_societe AND bullent.periode=Lc_periode AND !DELETED("bullent");
INTO CURSOR cursnet
SELECT bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bulltiers.societe as tiersoc,bulltiers.periode as tiersper,bulltiers.matricule as matiers,;
bulltiers.clef as cleftiers,bulltiers.tiersid,bulltiers.coderub as rubtiers,bulltiers.librub as libtiers,SUM(bulltiers.montant) as montiers,;
SUM(bulltiers.nbrebase) as nbretiers,SUM(bulltiers.taux) as tauxtiers, COUNT(*) as nbrecords_tiers;
FROM bullent ;
INNER Join bulltiers On bullent.entid=bulltiers.tiersid AND bullent.societe=bulltiers.societe AND bullent.matricule=bulltiers.matricule AND;
bullent.clef=bulltiers.clef AND bullent.periode=bulltiers.periode;
GROUP BY bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bulltiers.societe,bulltiers.periode,bulltiers.matricule,;
bulltiers.clef,bulltiers.tiersid,bulltiers.coderub,bulltiers.librub,bulltiers.montant,;
bulltiers.nbrebase,bulltiers.taux ;
WHERE bullent.societe=Lc_societe AND bullent.periode=Lc_periode AND !DELETED("bullent");
INTO CURSOR curstiers
SELECT bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullcum.cumid,bullcum.baseamo,bullcum.baseconge,;
bullcum.baseimpot,bullcum.baseinps,bullcum.jrscong,bullcum.jrspres,bullcum.retamo,bullcum.retimpot,bullcum.retinps,;
bullcum.salbrut, COUNT(*) as nbrecords_cum;
FROM bullent ;
INNER Join bullcum On bullent.entid=bullcum.cumid AND bullent.societe=bullcum.societe AND bullent.matricule=bullcum.matricule AND;
bullent.clef=bullcum.clef AND bullent.periode=bullcum.periode;
GROUP BY bullent.societe,bullent.periode,bullent.matricule,bullent.clef,bullent.entid,bullcum.cumid,bullcum.baseamo,bullcum.baseconge,;
bullcum.baseimpot,bullcum.baseinps,bullcum.jrscong,bullcum.jrspres,bullcum.retamo,bullcum.retimpot,bullcum.retinps,;
bullcum.salbrut ;
WHERE bullent.societe=Lc_societe AND bullent.periode=Lc_periode AND !DELETED("bullent");
INTO CURSOR curscum
SELECT cursdet.periode,cursdet.matricule,cursdet.clef,cursdet.entid,cursdet.coderub, ;
COUNT(*) AS nbrecordstmp ;
FROM cursdet ;
WHERE cursdet.societe=Lc_societe AND cursdet.periode=Lc_periode ;
GROUP BY cursdet.periode, cursdet.matricule, cursdet.clef,cursdet.entid, cursdet.coderub ;
INTO CURSOR bulltmp
SELECT bulltmp.nbrecordstmp, cursdet.*, ;
curscharg.rubcharg,curscharg.libcharg ,curscharg.montcharg, curscharg.nbrecharg, ;
curscharg.tauxcharg,curscharg.codcharg, cursnet.rubnet, cursnet.libnet, cursnet.montnet, ;
cursnet.nbrenet, cursnet.tauxnet, curstiers.rubtiers, curstiers.libtiers, curstiers.montiers, ;
curstiers.nbretiers, curstiers.tauxtiers, curscum.baseamo, curscum.baseconge, curscum.baseimpot, ;
curscum.baseinps,curscum.jrscong, curscum.jrspres, curscum.retamo, curscum.retimpot, ;
curscum.retinps, curscum.salbrut, NVL(societe.numsecu,SPACE(15)) as numorg, ;
NVL(societe.numfisc,SPACE(15)) as nfiscal, societe.libelabg as libsoc, ;
societe.datepaie AS datpaie,ville.libelong As libville, ;
NVL(motif.libelabg,SPACE(15)) as libmotif, nbull.libelle as libbull, ;
tfreq.libelle as libfreq, conge.datedebut, conge.datefin ;
FROM bulltmp ;
INNER JOIN cursdet ON bulltmp.periode = cursdet.periode AND bulltmp.matricule = cursdet.matricule ;
AND bulltmp.clef = cursdet.clef AND bulltmp.coderub= cursdet.coderub ;
Left Outer Join cursnet On cursdet.matricule=cursnet.matricule AND cursdet.clef=cursnet.clef AND ;
cursdet.societe=cursnet.societe AND cursdet.periode=cursnet.periode ;
INNER Join curscharg On cursdet.matricule=curscharg.matricule AND cursdet.clef=curscharg.clef AND;
cursdet.societe=curscharg.societe AND cursdet.periode=curscharg.periode;
INNER Join curstiers On cursdet.matricule=curstiers.matricule AND cursdet.clef=curstiers.clef AND;
cursdet.societe=curstiers.societe AND cursdet.periode=curstiers.periode;
INNER Join curscum On cursdet.matricule=curscum.matricule AND cursdet.clef=curscum.clef AND;
cursdet.societe=curscum.societe AND cursdet.periode=curscum.periode;
Left Outer Join societe On cursdet.societe=societe.societe;
Left Outer Join ville On societe.pays=ville.codpays AND societe.ville=ville.codvil;
Left Outer Join motif On cursdet.societe=motif.societe AND cursdet.nbull=motif.codmotif;
Left Outer Join nbull On cursdet.societe=nbull.societe AND cursdet.nbull=nbull.code;
Left Outer Join tfreq On cursdet.societe=tfreq.societe AND cursdet.nbull=tfreq.nbull AND cursdet.freq=tfreq.freq;
Left Outer Join conge On cursdet.matricule=conge.matricule AND cursdet.clef=conge.clef AND cursdet.societe=conge.societe AND;
cursdet.periode=conge.datepaie;
ORDER BY bulltmp.periode, bulltmp.matricule, bulltmp.clef, bulltmp.entid,bulltmp.coderub ;
INTO CURSOR bulletin
Permalink : http://www.atoutfox.org/nntp.asp?ID=0000019870