DSXchange

Posted: **Tue Apr 19, 2011 9:30 pm**

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

Posted: **Tue Apr 19, 2011 9:50 pm**

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.

Posted: **Wed Apr 27, 2011 4:17 pm**

Thank you Ray

DSXchange

horizontal sort

horizontal sort

sort