*------------------------------------------------------------------------- * t_stamp_ops_evt_tmp.bteq * Insertion des données *------------------------------------------------------------------------- * Date | Utilisateur | Commentaires *------------------------------------------------------------------------- * 19/08/2015 | ASTEK TS | Creation * ??/07/2017 | N BOUCHER | Refonte du calcul des routes et stations. * 13/10/2017 | N BOUCHER | UPDATE de la date des EVT niveau Container DR_REC0 * 14/03/2018 | N BOUCHER | Modification on Route ENd calculation *------------------------------------------------------------------------- /* ************************************************** */ /* Alimentation table STAMP_OPS_EVT_TMP */ /* Contient les Evènements au niveau Receptacle (Sac) */ /* et au niveau Container (CARDIT) */ /* ************************************************** */ *------------------------------------------------------------------------------ * Vidage de la table STAMP_OPS_EVT_TMP *------------------------------------------------------------------------------ DELETE FROM STAMP_OPS_EVT_TMP ALL; .if ERRORCODE <> 0 then .GOTO ERREUR_DELETE *-------------------------------------------------------------------------- * Debut de la transaction *-------------------------------------------------------------------------- .SET RETRY OFF BEGIN TRANSACTION; lock table STAMP_OPS_EVT_TMP for write; *------------------------------------------------------------------------------ * Alimentation de la table finale *------------------------------------------------------------------------------ INSERT INTO STAMP_OPS_EVT_TMP Select --ETAPE TMP7: Pour chaque station qui n est pas nouvelle (voir TMP5), on reporte le numéro précédent NUM_RTE --On reporte les numéro de station qui ne changent pas. ,case when NUM_STA is null then max(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) else NUM_STA end as NUM_STA ,COD_EVT ,IDT_CNT_RCU ,DAT_EVT_UTC ,DAT_LOC_EVT ,DAT_DEP ,LCT_SCA ,ESC_ORI ,ESC_DST ,NOM_PRN_ORI ,NOM_PRN_DST ,NUM_VOL_DEP ,NUM_VOL_ARV ,IDT_VOL FROM ( --ETAPE TMP6: Pour chaque route, pour chaque nouvelle station, On affecte un numéro croissant à chaque nouvelle station (voir TMP5) Select NUM_RTE --S il s agit d une nouvelle station (voir TMP5), on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de station ,case when NUM_STA=1 then coalesce(Count(NUM_STA) OVER (PARTITION BY IDT_CNT_RCU, NUM_RTE ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) else NUM_STA end as NUM_STA ,COD_EVT ,IDT_CNT_RCU ,DAT_EVT_UTC ,DAT_LOC_EVT ,DAT_DEP ,LCT_SCA ,ESC_ORI ,ESC_DST ,NOM_PRN_ORI ,NOM_PRN_DST ,NUM_VOL_DEP ,NUM_VOL_ARV ,IDT_VOL FROM ( -- ETAPE TMP5 Pour chaque route, on identifie les nouvelles stations avec un indicateur '1' Select NUM_RTE --indicateur de changement de station pour chaque route ,case when coalesce(min(LCT_SCA) over ( PARTITION BY IDT_CNT_RCU , NUM_RTE order by DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc rows between 1 preceding and 1 preceding), 0) <> LCT_SCA then 1 else null end as NUM_STA ,COD_EVT ,IDT_CNT_RCU ,DAT_EVT_UTC ,DAT_LOC_EVT ,DAT_DEP ,LCT_SCA ,ESC_ORI ,ESC_DST ,NOM_PRN_ORI ,NOM_PRN_DST ,NUM_VOL_DEP ,NUM_VOL_ARV ,IDT_VOL FROM ( --ETAPE TMP4: Pour les lignes qui ne sont pas un début de route, on reporte le numéro précédent Select case when NUM_RTE is null then -- Si la ligne n'a pas de prédécésseur alors on mets 1 sinon on mets la valeur du prédécesseur Case when max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) is null then 1 Else max(NUM_RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING) end else NUM_RTE end as NUM_RTE ,COD_EVT ,IDT_CNT_RCU ,DAT_EVT_UTC ,DAT_LOC_EVT ,DAT_DEP ,LCT_SCA ,ESC_ORI ,ESC_DST ,NOM_PRN_ORI ,NOM_PRN_DST ,NUM_VOL_DEP ,NUM_VOL_ARV ,IDT_VOL FROM ( --ETAPE TMP3 : On affecte un numéro croissant à chaque début de route SELECT distinct --s il s agit d un début de route, on effectue un compte les indicateurs sur les lignes pécédentes pour attribuer le bon numéro de début de route case when RTE=1 then coalesce(Count(RTE) OVER (PARTITION BY IDT_CNT_RCU ORDER BY DAT_EVT_UTC asc , case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ROWS UNBOUNDED PRECEDING),0) else RTE end as NUM_RTE ,TMP2.COD_EVT ,TMP2.IDT_CNT_RCU ,TMP2.DAT_EVT_UTC ,TMP2.DAT_LOC_EVT ,TMP2.DAT_DEP ,TMP2.LCT_SCA ,TMP2.ESC_ORI ,TMP2.ESC_DST ,TMP2.NOM_PRN_ORI ,TMP2.NOM_PRN_DST ,TMP2.NUM_VOL_DEP ,TMP2.NUM_VOL_ARV ,TMP2.IDT_VOL FROM ( --ETAPE TMP2: On identifie les début de route avec des indicateur '1' SELECT --On affecte un indicateur pour chaque début de route du Receptacle --s il s agit d un premier evènement (sauf iSC) sans début identifié case when ROW_NUMBER() over ( PARTITION BY IDT_CNT_RCU order by DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc ) = 1 AND COD_EVT <>'iSC' AND COD_EVT <>'6' then 1 --s il s agit bien d un début identifié when COD_EVT in ('74','43') then 1 -- s il n y a pas de début de route mais que la ligne précédente est une fin de route, alors on crée une nouvelle route quand même when coalesce(min(COD_EVT) over ( PARTITION BY IDT_CNT_RCU order by DAT_EVT_UTC ASC, case when COD_EVT in ('74','43') then '1000' when COD_EVT in ('21', '42') then '0' else COD_EVT end desc, DAT_DEP asc rows between 1 preceding and 1 preceding), '#') in ('21', '42') then 1 else null end as RTE ,TMP1.* FROM ( -- ETAPE TMP1: On reccupère les information de base SELECT 'RECEPTACLE' as LVL ,COD_EVT ,IDT_CNT_RCU ,DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC ,cast (NULL as Timestamp(0)) as NON_CORR_DAT_EVT_CNT_UTC ,DAT_EVT_UTC ,DAT_LOC_EVT ,DAT_DEP ,LCT_SCA ,ESC_ORI ,ESC_DST ,NOM_PRN_ORI ,NOM_PRN_DST ,NUM_VOL_DEP ,NUM_VOL_ARV ,IDT_VOL FROM DWHFRT.STAMP_EVT_CNT --Receptacle level WHERE IDT_TYP = 'R' UNION SELECT 'CONTAINER' as LVL ,EVT.COD_EVT ,CNT.IDT_CNT_RCU --DR_REC0 Event at the COntainer level must have the data of the max(STAMP_CNT_RCU.DAT_DEB_VLD) ,EVT.DAT_EVT_UTC as NON_CORR_DAT_EVT_UTC ,CNT.DAT_DEB_VLD as NON_CORR_DAT_EVT_CNT_UTC --,Las plus grande des deux dates :(CNT.DAT_DEB_VLD , EVT.DAT_EVT_UTC ) as DAT_EVT_UTC ,CAST( SUBSTRING (CAST ( TO_TIMESTAMP( GREATEST( COALESCE(TO_CHAR(CNT.DAT_DEB_VLD , 'yyyymmddhh24miss'),'19000101000000'), COALESCE(TO_CHAR(EVT.DAT_EVT_UTC, 'yyyymmddhh24miss'),'19000101000000') ),'yyyymmddhh24miss') AS VARCHAR(25)) FROM 1 FOR 19) AS TIMESTAMP(0) FORMAT 'YYYY/MM/DDBHH:MI:SS') AS DAT_EVT_UTC ,EVT.DAT_LOC_EVT ,EVT.DAT_DEP ,EVT.LCT_SCA ,EVT.ESC_ORI ,EVT.ESC_DST ,EVT.NOM_PRN_ORI ,EVT.NOM_PRN_DST ,EVT.NUM_VOL_DEP ,EVT.NUM_VOL_ARV ,EVT.IDT_VOL FROM DWHFRT.STAMP_EVT_CNT EVT INNER JOIN DWHFRT.STAMP_CNT_RCU CNT ON EVT.IDT_COD_CNT = CNT.IDT_COD_CNT --Container level WHERE EVT.IDT_TYP = 'C' AND CNT.IDT_TYP = 'R' ) as TMP1 --where IDT_CNT_RCU='AEDXBASESTOABTT70050001000054' ) as TMP2 ) as TMP3 ) as TMP4 ) as TMP5 ) as TMP6 --order by 4,5 ; .if ERRORCODE <> 0 then .GOTO ERREUR_INSERT *-------------------------------------------------------------------------- * Fin de la transaction *-------------------------------------------------------------------------- END TRANSACTION; *-------------------------------------------------------------------------- * Collecte des statistiques sur index primaire *-------------------------------------------------------------------------- COLLECT STATISTICS ON STAMP_OPS_EVT_TMP INDEX PI_STAMP_OPS_EVT_TMP; .if ERRORCODE <> 0 then .GOTO ERREUR_COLLECT *-------------------------------------------------------------------------- * Sortie ok *-------------------------------------------------------------------------- -- Fin du traitement OK .LABEL SORTIE_OK .QUIT 0 *-------------------------------------------------------------------------- * Pb lors du chargement de la table *-------------------------------------------------------------------------- .LABEL ERREUR_DELETE .os echo "Erreur delete table STAMP_OPS_EVT_TMP" .quit 12 .LABEL ERREUR_INSERT .os echo "Erreur insert dans STAMP_OPS_EVT_TMP" .quit 12 .LABEL ERREUR_COLLECT .os echo "Erreur collect stats table STAMP_OPS_EVT_TMP" .quit 12