This is my problem.
I have a fact table named CMOUVEDT.
I have another table which contains some lookup data : CACCOUTB.
I want to extract each lookup in simple data tables:
This is the script :
CMOUVEDT : LOAD MNGSOCCD, MNGPOSCD, DCEXPRCD as PERIODE, DOCPERCD as MOIS, DOCEXECD as ANNEE, JRNCODCD as JOURNAL, DOCNUMCD as PIECE, DOCLINCD as LIGNE, DOCDATCD, JRNTYPCD as JOURNAL_TYPE, GENACCCD as COMPTE, CRLCCUCD - DELCCUCD as MONTANT, ANAACCCD, FLRREFCD, COMNUMCD, SALNBRCD, PURNBRCD, MVTDSCCD as DESCRIPTION, CREUSRCD, CREDATCD, UPTUSRCD, UPTDATCD ; SQL SELECT MNGSOCCD, MNGPOSCD, DCEXPRCD, DOCPERCD, DOCEXECD, JRNCODCD, DOCNUMCD, DOCLINCD, DOCDATCD, JRNTYPCD, GENACCCD, DELCCUCD , CRLCCUCD, ANAACCCD, FLRREFCD, COMNUMCD, SALNBRCD, PURNBRCD, MVTDSCCD, CREUSRCD, CREDATCD, UPTUSRCD, UPTDATCD from CMOUVEDT where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2);; ; //* LU_FOURNISSEURS : LOAD * ; SQL select COMNUMCP as COMNUMCD ,COMALPCP as CODE_FOURNISSEUR, COMNA1CP as FOURNISSEUR from ECOMPYTB where MNGSOCCP = '001' ;; LU_PRODUITS : LOAD * ; SQL select ACCCODAC as ANAACCCD //,ACCDS1AC ,ACCALPAC as PRODUIT from CACCOUTB where ACCTYPAC = 'ANA' and MNGSOCAC = '001' and MNGPOSAC = '002' ;; LU_DOSSIERS : LOAD * ; SQL SELECT ACCCODAC as FLRREFCD ,ACCALPAC as DOSSIER from CACCOUTB where ACCTYPAC = 'FLD' and MNGSOCAC = '001' and MNGPOSAC = '002' ;; LU_COMPTES : LOAD *; SQL select ACCCODAC as GENACCCD ,ACCDS1AC as COMPTE_LIB, ACCALPAC, LE8001AC as COMPTE_GR1, LE8002AC as COMPTE_GR2, LE8003AC as COMPTE_GR3, LE8004AC as COMPTE_GR4, LE8005AC as COMPTE_GR5 from CACCOUTB where ACCTYPAC = 'GEN' and MNGSOCAC = '001' and MNGPOSAC = '000' ;; LU_PROJETS : LOAD * ; SQL select NBRPRJPD as PURNBRCD ,ACCALPPD as PROJET,DESCR1PD from EPRDETDT where MNGSOCPD = '001'and MNGPOSPD = '002' ;; lu_services : SQL select CONNUMCN as SALNBRCD ,CONNA1CN as SERVICE from ECONTATB where MNGSOCCN = '001' and MNGPOSCN = '002' ;;
So, I would like create another table named COMPTA, that "left join" CMOUVEDT with lookup table.
compta : load * resident CMOUVEDT; left join LOAD * Resident LU_COMPTES ; left join LOAD * Resident LU_DOSSIERS; left join load * resident LU_PRODUITS; left join load * resident LU_FOURNISSEURS; left join load * resident LU_SERVICES; left join load * resident LU_PROJETS;
But the table named Compta is not created.
Why creating this table ? Because a need scan it later : i.e : select * from compta where .....
What the good way to do this ?
Thanks in advance.