Avoid loops

[souadouert]

i tried to found a solution to avoid loops in my modele

SCRIPT

tregion:

SQL SELECT CODEREGION,

    LIBELLEREGION

FROM REGION;

tzone:

SQL SELECT CODEREGION,

    CODEZONE,

    LIBELLEZONE

FROM TZONE;

tagence:

SQL SELECT ADRESS,

    CHEFAGENCE,

    CODEAGENCE ,

    CODEZONE

FROM AGENCE;

tobjectifagence:

SQL SELECT CODEAGENCE as CODEAGENCE ,

OBJECTIF,

    CODEOBJECTIF  ,

    CODEPRODUIT,

    DATEOBJECTIF

FROM OBJECTIFAGENCE;

trealisationagence:

SQL SELECT CODEAGENCE as CODEAGENCE,

     REALISATION,

    CODEPRODUIT  ,

    CODEVENTE,

    DATEVENTE 

FROM VENTEPRODUITAGENCE;

calendrier:

SQL SELECT

    Switch( day(`DATEVENTE`)<16,"Q1",

              day(`DATEVENTE`)>15,"Q2") as 'quinzaine',

   DAY(`DATEVENTE`) as jour,

     MONTH(`DATEVENTE`) as  mois,

     Switch(

     MONTH(`DATEVENTE`)=1,"Janvier",

     MONTH(`DATEVENTE`)=2,"Février",

     MONTH(`DATEVENTE`)=3,"Mars",

     MONTH(`DATEVENTE`)=4,"Avril",

     MONTH(`DATEVENTE`)=5,"Mai",

     MONTH(`DATEVENTE`)=6,"Juin",

     MONTH(`DATEVENTE`)=7,"Juillet",

     MONTH(`DATEVENTE`)=8,"Août",

     MONTH(`DATEVENTE`)=9,"Septembre",

     MONTH(`DATEVENTE`)=10,"Octobre",

     MONTH(`DATEVENTE`)=11,"Novembre",

     MONTH(`DATEVENTE`)=12,"Decembre") as NOMMOIS,

     Switch(

     MONTH(`DATEVENTE`)=1,"Janv.",

     MONTH(`DATEVENTE`)=2,"Fév.",

     MONTH(`DATEVENTE`)=3,"Mars",

     MONTH(`DATEVENTE`)=4,"Avr.",

     MONTH(`DATEVENTE`)=5,"Mai",

     MONTH(`DATEVENTE`)=6,"Juin",

     MONTH(`DATEVENTE`)=7,"Juill.",

     MONTH(`DATEVENTE`)=8,"Août",

     MONTH(`DATEVENTE`)=9,"Sept",

     MONTH(`DATEVENTE`)=10,"Oct.",

     MONTH(`DATEVENTE`)=11,"Nov.",

     MONTH(`DATEVENTE`)=12,"Dec.") as CODEMOIS,

      YEAR(`DATEVENTE`) as année,

      DATEVENTE

FROM `VENTEPRODUITAGENCE`

order by `DATEVENTE`;

tproduit:

SQL SELECT CODEPRODUIT ,

CODEPRODUIT,

    LIBELLEPRODUIT

FROM Produit;

AND THIS IS THE MODEL/

[adamdavi3s]

Technically there is nothing at all wrong with a synthetic key However you can work around it with a quick link table or you can concatenate the fields to make a key to join on (I prefer links myself)

[souadouert]

good morning adam , i know that, But Preferably in MDD , we have to avoid this type of loop.

[adamdavi3s]

Hi Souad,

I suspect I am missing the point?

Is it the synthetic key you are wanting to remove from the data model?

[souadouert]

yess

[adamdavi3s · Accepted solution]

Ok please can you try this out, sorry there might be some small mistakes as obviously I can't try it out (don;t have access to your DB to load)

tregion:

SQL SELECT CODEREGION,

    LIBELLEREGION

FROM REGION;

tzone:

SQL SELECT CODEREGION,

    CODEZONE,

    LIBELLEZONE

FROM TZONE;

tagence:

SQL SELECT ADRESS,

    CHEFAGENCE,

    CODEAGENCE ,

    CODEZONE

FROM AGENCE;

tobjectifagence:

LOAD *, rowno() as tobjectifagence_id;

SQL SELECT CODEAGENCE as tobjectifagence_CODEAGENCE ,

OBJECTIF,

    CODEOBJECTIF  ,

    CODEPRODUIT as tobjectifagence_CODEPRODUIT,

    DATEOBJECTIF

FROM OBJECTIFAGENCE;

trealisationagence:

LOAD *, rowno() as trealisationagence_id;

SQL SELECT CODEAGENCE as trealisationagence_CODEAGENCE,

     REALISATION,

    CODEPRODUIT as trealisationagence_CODEPRODUIT  ,

    CODEVENTE,

    DATEVENTE

FROM VENTEPRODUITAGENCE;

calendrier:

SQL SELECT

    Switch( day(`DATEVENTE`)<16,"Q1",

              day(`DATEVENTE`)>15,"Q2") as 'quinzaine',

   DAY(`DATEVENTE`) as jour,

     MONTH(`DATEVENTE`) as  mois,

     Switch(

     MONTH(`DATEVENTE`)=1,"Janvier",

     MONTH(`DATEVENTE`)=2,"Février",

     MONTH(`DATEVENTE`)=3,"Mars",

     MONTH(`DATEVENTE`)=4,"Avril",

     MONTH(`DATEVENTE`)=5,"Mai",

     MONTH(`DATEVENTE`)=6,"Juin",

     MONTH(`DATEVENTE`)=7,"Juillet",

     MONTH(`DATEVENTE`)=8,"Août",

     MONTH(`DATEVENTE`)=9,"Septembre",

     MONTH(`DATEVENTE`)=10,"Octobre",

     MONTH(`DATEVENTE`)=11,"Novembre",

     MONTH(`DATEVENTE`)=12,"Decembre") as NOMMOIS,

     Switch(

     MONTH(`DATEVENTE`)=1,"Janv.",

     MONTH(`DATEVENTE`)=2,"Fév.",

     MONTH(`DATEVENTE`)=3,"Mars",

     MONTH(`DATEVENTE`)=4,"Avr.",

     MONTH(`DATEVENTE`)=5,"Mai",

     MONTH(`DATEVENTE`)=6,"Juin",

     MONTH(`DATEVENTE`)=7,"Juill.",

     MONTH(`DATEVENTE`)=8,"Août",

     MONTH(`DATEVENTE`)=9,"Sept",

     MONTH(`DATEVENTE`)=10,"Oct.",

     MONTH(`DATEVENTE`)=11,"Nov.",

     MONTH(`DATEVENTE`)=12,"Dec.") as CODEMOIS,

      YEAR(`DATEVENTE`) as année,

      DATEVENTE

FROM `VENTEPRODUITAGENCE`

order by `DATEVENTE`;

tproduit:

SQL SELECT CODEPRODUIT   ,

CODEPRODUIT,

    LIBELLEPRODUIT

FROM Produit;

LinkTable:

LOAD tobjectifagence_id,

tobjectifagence_CODEAGENCE as CODEAGENCE,

tobjectifagence_CODEPRODUIT as CODEPRODUIT

resident tobjectifagence;

Concatenate

LOAD trealisationagence_id,

trealisationagence_CODEAGENCE as CODEAGENCE,

trealisationagence_CODEPRODUIT as CODEPRODUIT

resident trealisationagence;

drop fields tobjectifagence_CODEAGENCE,tobjectifagence_CODEPRODUIT,trealisationagence_CODEAGENCE,trealisationagence_CODEPRODUIT;

[souadouert]

thank you adam   You helped me a lot

[adamdavi3s]

No problem at all, glad to be of help