Retrieving first and last record from a table/file

Retrieving the first and last record from a table/file 

Source  Table: OT_SRC_DEV1.Employees

.

.

.

.

.

.

Step 1: Drag the Source on Source Analyzer

Step 2: Drag the Target to Target Designer 

Step 3: Use Sequence Generator Transformation with Rank Transformation to find Top and Bottom Record

Step 4: Connect Sequence Generator NEXTVAL to both Rank Transformation 

Step 5: Set The First Rank Transformation to Top and the number of Ranks to 1 in the properties. 

Step 6: Set The Second Rank Transformation to Bottom and the number of Ranks to 1 in the properties. 

Step 7: Use Union Transformation to join both the Rank Transformation with Two groups (Top and Bottom) as shown in the figure. 

Step 8: Connect the Rank Transformation to Union Transformation in port and Union Transformation to Target as shown below. 

Step 9: Create the workflow and set the source and target relational connections and run the workflow. 

Step 10: Check the details in Workflow Monitor for Properties and Logs for any errors and debugging. 

The Final Transformation is Shown below. 

Target Table: OT_TGT_DEV1.flrec_employees;