UNIT: 4 Expression, Filter, File Lists and Workflow Scheduler
4-1. Expression Transformation
1) Designer
Source Analyzer
Import file - customer_layout.txt
first line, comma 구분, Double Quotes, Date filed is Datetime
Target Designer
Import database - tdbu07 접속 - STG_CUSTOMERS
Mapping Designer
Create - m_Stage_Customer_Contacts_07
customer_layout 불러오기
STG_CUSTOMERS 불러오기
Filter 생성
CUSTOMER_NO
FIRSTNAME
LASTNAME
ADDRESS
CITY
STATE
ZIP
COUNTRY
PHONE_NUMBER
GENDER
INCOME
AGE
rename - fil_Customer_No_99999
properties 탭 선택
Filter Condition - CUSTOMER_NO != 99999 OR ISNULL(CUSTOMER_NO)
OK
Expression Transformation 생성
Filter 에서
FIRSTNAME
LASTNAME
PHONE_NUMBER
GENDER
AGE
연결
rename - exp_Format_Name_Gender_Phone
port 탭에서
AGE 빼고 모든 컬럼 output 해제 후 IN_ 붙이기
OUT_CUST_NAME 생성, STRING, 41, IN_FIRSTNAME ||' ' ||IN_LASTNAME
OUT_CUST_PHONE 생성, STRING, 14, '(' || SUBSTR(TO_CHAR(IN_PHONE_NUMBER),1,3) || ') ' ||
SUBSTR(TO_CHAR(IN_PHONE_NUMBER),4,3) ||'-' ||
SUBSTR(TO_CHAR(IN_PHONE_NUMBER),7,4)
OUT_GENDER 생성, STRING, 6, DECODE(IN_GENDER,
'M', 'MALE',
'F', 'FEMALE',
'UNK')
OUT_AGE_GROUP 생성, STRING, 20, DECODE(TRUE,
AGE < 20, 'LESS THAN 20',
AGE >= 20 AND AGE <= 29, '20 TO 29',
AGE >= 30 AND AGE <= 39, '30 TO 39',
AGE >= 40 AND AGE <= 49, '40 TO 49',
AGE >= 50 AND AGE <= 60, '50 TO 60',
AGE > 60, 'GREATER THAN 60')
저장
EXPRESSION TRANSFORMATION 부터 TARGET 연결
AGE CUST_AGE
OUT_CUST_NAME J CUST_NAME
OUT_CUST_PHONE J CUST_PHONE_NMBR
OUT_GENDER J CUST_GENDER
OUT_AGE_GROUP J CUST_AGE_GROUP
FILTER TRANSFORMATION 부터 TARGET 연결
CUSTOMER_NO J CUST_ID
ADDRESS J CUST_ADDRESS
CITY J CUST_CITY
STATE J CUST_STATE
ZIP J CUST_ZIP_CODE
COUNTRY J CUST_COUNTRY
INCOME J CUST_INCOME
EMAIL J CUST_E_MAIL
저장
우측 버튼 Arrange All Iconic 선택
2) Workflow Manger
Workflow - Create - wkf_Stage_Customer_Contacts_07
session 버튼 클릭 - m_Stage_Customer_Contacts_07
Start 부터 link
Edit - Mapping 탭
SQ_customer_layout - file name - customer_list.txt 로 변경
filetype - Indirect 로 변경
STG_CUSTOMERS 는 TDBU 로 변경
Truncate check
저장
start workflow
3) Schedule
Workflows - Edit - Scheduler 탭 클릭
Edit 버튼 클릭 - sch_Stage_Customer_Contacts_07
Schedule 탭
customized repeat edit 클릭
week 선택, 월화수목금 선택, run once 선택
ok
내일 날짜, 00:01 선택 , forever 선택
ok
우측 버튼 schedule workflow 선택
dhzpdl
4-2. Filter Transformation
4-3. Free Lists
4-4. Workflow Scheduler