horizontal sort

[vam]

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

[ray.wurlod]

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.

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 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.

[vam]

Thank you Ray