import / import_iwm_mail_addresses

Engine: Import
Source_Config: oster400_adminecomm
Target_Config: importEngine
Mysql_Engine: InnoDB
Default_Charset: utf8
Source_Connection_Type: database
Source_Database_Connection_Service: DatabaseConnectionService

Queries:
 - Query:
     TargetDatapool: mail_addresses
     GetQuery: SELECT
               T1.EMKNDN,
               REPLACE(TRIM(T1.EMEMAL), '.@', '@') AS EMAIL,
               CASE
               WHEN T1.EMDAEJ IS NULL OR T1.EMDAEM IS NULL OR T1.EMDAET IS NULL OR
               T1.EMDAEJ = 0 OR T1.EMDAEM = 0 OR T1.EMDAET = 0 OR
               T1.EMDAEJ < 1000 OR T1.EMDAEJ > 9999 OR
               T1.EMDAEM < 1 OR T1.EMDAEM > 12 OR
               T1.EMDAET < 1 OR T1.EMDAET > 31
               THEN NULL
               ELSE DATE(TIMESTAMP_FORMAT(
               LPAD(CAST(T1.EMDAET AS VARCHAR(2)), 2, '0') || '.' ||
               LPAD(CAST(T1.EMDAEM AS VARCHAR(2)), 2, '0') || '.' ||
               CAST(T1.EMDAEJ AS VARCHAR(4)),
               'DD.MM.YYYY'
               ))
               END AS aedat,
               MAX(T1.EMLFN1) AS MaxvonEMLFN1,
               CASE
               WHEN T2.VDSOLV IN ('B', 'N', 'M') THEN 'N'
               WHEN T2.VDSOLV IN ('J', 'E') THEN 'J'
               ELSE NULL
               END AS WERBKZ,
               TRIM(T4.ADAVOR) AS VORNAME,
               TRIM(T4.ADNNAM) AS NACHNAME,
               T2.VDFIRM,
               T2.VDSOLV,
               T4.ADPL15,
               T4.ADLORT,
               T4.ADANRD
               FROM
               IWMOST1DTA.EMAL01 T1
               LEFT JOIN
               IWMV2R1DTA.VDAD00 T2 ON T1.EMKNDN = T2.VDKNDN
               LEFT JOIN
               IWMADROLIB.ADRS01 T4 ON T1.EMANUM = T4.ADANUM
               GROUP BY
               T1.EMKNDN,
               REPLACE(TRIM(T1.EMEMAL), '.@', '@'),
               CASE
               WHEN T1.EMDAEJ IS NULL OR T1.EMDAEM IS NULL OR T1.EMDAET IS NULL OR
               T1.EMDAEJ = 0 OR T1.EMDAEM = 0 OR T1.EMDAET = 0 OR
               T1.EMDAEJ < 1000 OR T1.EMDAEJ > 9999 OR
               T1.EMDAEM < 1 OR T1.EMDAEM > 12 OR
               T1.EMDAET < 1 OR T1.EMDAET > 31
               THEN NULL
               ELSE DATE(TIMESTAMP_FORMAT(
               LPAD(CAST(T1.EMDAET AS VARCHAR(2)), 2, '0') || '.' ||
               LPAD(CAST(T1.EMDAEM AS VARCHAR(2)), 2, '0') || '.' ||
               CAST(T1.EMDAEJ AS VARCHAR(4)),
               'DD.MM.YYYY'
               ))
               END,
               CASE
               WHEN T2.VDSOLV IN ('B', 'N', 'M') THEN 'N'
               WHEN T2.VDSOLV IN ('J', 'E') THEN 'J'
               ELSE NULL
               END,
               TRIM(T4.ADAVOR),
               TRIM(T4.ADNNAM),
               T2.VDFIRM,
               T2.VDSOLV,
               T1.EMSTAT,
               T4.ADPL15,
               T4.ADLORT,
               T4.ADANRD
               HAVING
               MAX(T1.EMLFN1) > 0
               AND T1.EMKNDN > 0
               AND REPLACE(TRIM(T1.EMEMAL), '.@', '@') IS NOT NULL
               AND REPLACE(TRIM(T1.EMEMAL), '.@', '@') <> ''
               AND T1.EMSTAT = 'A'
   #  GetQuery: SELECT * from IWMOST1DTA.EMAL01
   #            WHERE EMSTAT='A' AND (WERBKZ='J' OR WERBKZ='E')
   #          #  GROUP BY EMKNDN, VNAME, NNAME, EMEMAL, WERBKZ, FIRM
   #          #  HAVING (WERBKZ='J' OR WERBKZ='E') AND FIRM=1

  #SELECT tbl_emailadresse.EMKNDN, tbl_emailadresse.VNAME, tbl_emailadresse.NNAME, tbl_emailadresse.EMEMAL, tbl_emailadresse.WERBKZ, IIf([AEDAT]>0,[AEDAT],"") AS DATAK
  #FROM tbl_emailadresse
  #GROUP BY tbl_emailadresse.EMKNDN, tbl_emailadresse.VNAME, tbl_emailadresse.NNAME, tbl_emailadresse.EMEMAL, tbl_emailadresse.WERBKZ, IIf([AEDAT]>0,[AEDAT],""), tbl_emailadresse.FIRM
  #HAVING (((tbl_emailadresse.WERBKZ)="J" Or (tbl_emailadresse.WERBKZ)="E") AND ((tbl_emailadresse.FIRM)=1));


  #SELECT IWMOST1DTA_EMAL01.EMKNDN, TrimExtended([EMEMAL]) AS EMAIL, CDate([EMDAET] & "." & [EMDAEM] & "." & [EMDAEJ]) AS aedat, Max(IWMOST1DTA_EMAL01.EMLFN1) AS MaxvonEMLFN1, tbl_emalwerbkz.WERBKZ, TrimExtended([ADAVOR]) AS VORNAME, TrimExtended([ADNNAM]) AS NAME, IWMV2R1DTA_VDAD00.VDFIRM
  #FROM ((IWMOST1DTA_EMAL01 LEFT JOIN IWMV2R1DTA_VDAD00 ON IWMOST1DTA_EMAL01.EMKNDN = IWMV2R1DTA_VDAD00.VDKNDN) LEFT JOIN tbl_emalwerbkz ON IWMV2R1DTA_VDAD00.VDSOLV = tbl_emalwerbkz.VDSOLV) LEFT JOIN IWMADROLIB_ADRS01 ON IWMOST1DTA_EMAL01.EMANUM = IWMADROLIB_ADRS01.ADANUM
  #GROUP BY IWMOST1DTA_EMAL01.EMKNDN, TrimExtended([EMEMAL]), CDate([EMDAET] & "." & [EMDAEM] & "." & [EMDAEJ]), tbl_emalwerbkz.WERBKZ, TrimExtended([ADAVOR]), TrimExtended([ADNNAM]), IWMV2R1DTA_VDAD00.VDFIRM, IWMOST1DTA_EMAL01.EMSTAT
  #HAVING (((IWMOST1DTA_EMAL01.EMKNDN)>0) AND ((TrimExtended([EMEMAL])) Is Not Null Or (TrimExtended([EMEMAL]))="") AND ((IWMOST1DTA_EMAL01.EMSTAT)="A"));