import / import_IWM_newsletter_tre

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,
               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,
              TRIM(T4.ADAVOR) AS VORNAME,
              TRIM(T4.ADNNAM) AS NAME,
              T2.VDFIRM
              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,
             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,
             TRIM(T4.ADAVOR),
             TRIM(T4.ADNNAM),
             T2.VDFIRM,
             T1.EMSTAT
             HAVING
             T1.EMKNDN > 0
             AND TRIM(T1.EMEMAL) IS NOT NULL
             AND 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"));