I have a sql query that I execute in a data mapping and gives me a list. I would like to, without a data splitter, retrieve the first and last element of the list in the mapping where I execute the query.
This can be achieved by following the below two approaches:
Solution 1: Try creating a Local Variable like "sort" on the root node of the target schema. In this local variable use a DB Query (Select * from TableName order by ColumnName,$Connection,'true') to fetch all the records from a database in sorted manner. Then Apply below ForEach on the Record node of the target schema that will get the first and last record:
$sort/Record[position()=1 or position()=count($sort/Record)]
Solution 2: While creating DB Source Schema activity use a SQL Query that sorts the records in ascending order. Then in Data mapping apply below For-Each on the Record node of the target schema that will get the first and last record:
$Input_Database_Source_Schema/Root/Record[position()=1 or position()=count($Input_Database_Source_Schema/Root/Record)]