Search

Custom Search

Thursday, March 18, 2010

Alternate Value of two fields in one column

I just found a very good tool that can find and replace any files that I selected with the set of old and new values I added all at the same time. I can even select a batch of folders to look for and millions of text that needs to be replaced with just a blink of an eye. This solves our problem with Hyperion where we need to migrate all the reports from an old system to a new system where with an all new sets of account TRULY HORRIBLE. As a programmer we don't want just to sit in my computer and replace each of the account with the new account manually. So when we found a way to automate the conversion we are all so happy that a report that can be converted in one whole day now can be converted in just a few minutes.

The ReplaceText Tool have an 'Import Replace Table' where you can import the set of old value and new value to its table in a text format.

But the problem is the text format is just one column where the old and new values just alternates on each line.

The challenge is how can I write a query where I could have one columns and the value of the old and new alternates on the row.

As fast as the Replace Text tool I have write the solution in SQL script:

SELECT Field FROM

((SELECT '>'+ OldAccount + '<' AS Field,
(((ROW_NUMBER() OVER(Order by ID))-1)*2 + 1) AS FieldOrder
FROM ConvertedAccounts)
UNION ALL
(SELECT '>'+NewAccount + '<' AS Field,
(((ROW_NUMBER() OVER(Order by ID)))*2) AS FieldOrder
FROM ConvertedAccounts)) A
ORDER BY FieldOrder
END


Adsense Banner