Hello Guru's,
can we do Horizontal sort(sort between columns) in datastage?
The source is flat file and target is oracle table.
i got data as follows,col1 is the key column.
col1,col2,col3,col4,col5,col6
A,2011/01/20,2008/05/18,2009/12/16,1998/96/12,2002/09/19
i need to get the data in the folloing order.
col1,col2,col3,col4,col5,col6
A,1998/96/12,2002/09/19,2008/05/18,2009/12/16,2011/01/20
can we do this in datastage?? i know we can do this in excel but am not sure how we can do it in datastage.
Thank you
vam
horizontal sort
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Create a routine. Call the routine from a stage variable. Load all the columns into a dynamic array and have the routine sort the dynamic array. Unpack the dynamic array into the output columns. Easy!!!
Here is an example of such a routine, which produced the result you indicated.
For descending left-justified sort, change By "AL" to By "DL" in the Locate statement.
For numeric sort, change the "L" to "R" in the By clause of the Locate statement.
Here is an example of such a routine, which produced the result you indicated.
Code: Select all
FUNCTION SortDynamicArray(aDynamicArray)
$UNDEFINE TESTING
If UnAssigned(aDynamicArray) Or IsNull(aDynamicArray)
Then
Ans = @NULL
End
Else
vDynamicArray = aDynamicArray
$IFDEF TESTING
Convert "~" To @FM In vDynamicArray
$ENDIF
Ans = ""
Loop
Remove vElement From vDynamicArray Setting bMoreElements
Locate vElement In Ans By "AL" Setting iPosition
Then
Ins vElement Before Ans<iPosition>
End
Else
Ins vElement Before Ans<iPosition>
End
While bMoreElements
Repeat
End
$IFDEF TESTING
Convert @FM To "~" In Ans
$ENDIF
RETURN(Ans)
For numeric sort, change the "L" to "R" in the By clause of the Locate statement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.