(java控件学习)Implementing FOR Loop in Informatica

1、 Implementing FOR loop inInformatica PowerCenterApplies to:Informatica PowerCenterSummaryThis article briefs about implementing FOR loop in Informatica using Java Transformation.Author BioAuthor(s:Sukumar BalasubramanianCompany:CIBCCreated on: November 17, 2009Sukumar Balasubramanian is an experienc

2、ed Informatica ETL Consultant working with CIBC, Canada. He has good exposure to Data Integration/Data Warehousing Projects. He is also a key contributor in informatica-l group of ittoolbox .This is his first technical article in TechNet.Table of ContentsINTRODUCTION (3REQUIREMENT (3SOURCE (3TARGET

3、(3SOLUTION OVERVIEW (4SOLUTION STEPS (4SOURCE (4EXPRESSION TRANSFORMATION (4JAVA TRANSFORMATION (5TARGET (6OUTPUT (6ADVANTAGES (7DISCLAIMER AND LIABILITY NOTICE (8INTRODUCTIONThis article briefs about implementing FOR loop in Informatica using Java Transformation. REQUIREMENTConsider that we are rec

4、eiving a | delimited flat file that contains the sales persons tour information. SOURCESalesperson Name | Tour from Date | Tour to Date | PlaceSukumar | 20091117 | 20091119 | ChennaiRam | 20091117 | 20091119 | Bangalore Sukumar | 20091120 | 20091122 | BangaloreRam | 20091120 | 20091122 | ChennaiOur

5、requirement is to generate Tour Table TARGET that contains the followingTARGETSalesperson Name Date PlaceSukumar 20091117ChennaiSukumar20091118ChennaiSukumar20091119ChennaiSukumar 20091120BangaloreSukumar20091121BangaloreSukumar20091122BangaloreRam 20091117BangaloreRam20091118BangaloreRam20091119Ban

6、galoreRam 20091120ChennaiRam20091121ChennaiRam20091122ChennaiSOLUTION OVERVIEWTo achieve the above requirement we will be using the Java Transformation available in Informatica PowerCenter.SOLUTION STEPSSOURCEDelimited Flat File TourInfo.txtSalesperson Name | Tour from Date | Tour to Date | PlaceSuk

7、umar | 20091117 | 20091119 | ChennaiRam | 20091117 | 20091119 | BangaloreSukumar | 20091120 | 20091122 | BangaloreRam | 20091120 | 20091122 | Chennai EXPRESSION TRANSFORMATIONPort Name Data Type Type ExpressionDescription String(255 ISalesperson Name String(50 O Use the substring, instr function tor

8、etrieve the sales person nameusing the | delimiter.FromDate String(20 V Use the substring, instr function toretrieve the From Date using the |delimiter.ToDate String(20 V Use the substring, instr function toretrieve the To Date name usingthe | delimiter.Place String(20 O Use the substring, instr fun

9、ction toretrieve the Place using the |delimiter.FromDate_out Date time O To_date(FromDate,YYYYMMDDToDate_out Date time O To_date(ToDate,YYYYMMDD Expression transformation is used for retrieving the values from the flat file source and assigns values to individual ports.JAVA TRANSFORMATIONDrag the ja

10、va transformation into the Mapping.Create the following ports.Port Name Data Type TypeSalesperson Name String(50 I/OFromDate String(20 IToDate String(20 IPlace String(20 I/OFromDate_out Date time OToDate_out Date time OTourDate DateTime OLink the FromDate_out, toDate_out from the Expression Transfor

11、mation to the From Date and To Date ports of the Java transformation.Under the Java code tab; Import Package tab place the below code:This is to import relevant java packages.Under "On InputRow " tab place the following codeDateFormat formatter ;formatter = new SimpleDateFormat("dd-MMM-yy"FromDate_out =formatter.format(FromDate;ToDate_out =formatter.format(ToDate;Calendar startCal = Calendar.getInstance ( ;Calendar endCal = Calendar.getInstance ( ;Date date_start=new Date(FromDate.longValue