DSXchange

Hi all
The requirement, is like..
I have about 300 input columns which inturn needs to be concatenated using '|' as the delimeter.
Eg: "Case number=':value in COL1|"Case Type=":value in COL2 so on COL300.
The second part of the requirement is , if the value between 2 delimeters exceeds more than 20 characters, add another delimeter at the end of the 20th character in addition to the delimeter at the end of the column.
Eg : Case number=1235732|7583|Case Type=Normal Cas|e without number|..

Any help is highly appreciated .

Tricky. Will there always be an '=' character? If so maybe you could use that to identify where your values are.

Could there be an '=' character anywhere else other than after the value name? Could the value name be more than 20 characters long? If the answer to either of these questions is "yes" then I think you have an unsolvable problem. So long as they are both "no", you can just find the = signs and then work backwards to the preceding '|' character to get your value name, and then work forwards from the '=' until you meet another '=' character and then work back from that to the preceding '|' and that's where the value ends.

well.. the "Case Name=", "Case Number=" are all constants..its like
I'll have to take the literal " Case Name=" and pass teh case name column as the value for the literal.
As of now, what i've done is, i've concatenated all the 300 hundred literals and the columns with | as the delimeter, so now i have a long string with | as the delimeter.. what I need now is the way to find if the literal and the value exceeds the length of 20, if it does, i need to add a delimeter at the end of the 20th character.

Fold() with Convert() in a basic transform can help. But as I won't advise you to use Basic Transformer, unless you have too long strings to convert or string of unknown length, you should the string more than 20 character long using substring "[]" with concatanation, that too before concatanating all 300 columns to make the task a bit easier.

There are many ways to do that.

But before going to solution - why do you need that ? Knowing the requirement may help better answers.

well, the need is for the GUI, specially for the delimeter requirement, since more than 20 chars wouldn't fit in the screen, hence I'm asked to add a delimeter.

If the source is coming from a db - like Oracle, you can use field format and wraps in SQL to achieve that.

well.. to answer this question, yes the incoming source is a oracle table, which has about 300 columns.
Here's what i have to be more clearer.
Case Name=DSLink1.Col1|Case Number=DSLink1.Col2|Case Person=DSLink1.Col3|Case Validity=DSLink1.Col4.. so on and so forth till Col300
where Case Name etc is the literal and DSLink.. is the value within the column.

Now, the outstanding issue is Literal+Value, if greater than 20 chars add another delimeter.

Did you look into SQLPlus formatting ?

You can query user_tab_columns and similar tables to obtain all column names - using which you can build your own query on the fly.

About the wrapping, you can set the wrap character and length for each field as format headers - again in runtime.

I was trying the wrapping of text using datastage, do you have any suggestions on that ?

Any suggestions on Datastage team ??

If there were, they'd be posted.

I already gave the solution, you can otherwise write a parallel routine to so the same.

Use Transformer and in its derivation for column use the way you want the output to be------
"Case No=":COL1:"|":"Case No=":COL2:"|":"Case No=":COL3:"|": ................................:"Case No=":COL300"

This way you have 300 rows in input and one in output

And as you want to break if the number of characters is more than 20 you need to use the if-then-else block as under:

"Col1=":
If Len(Col1) < 15
Then
Col1
Else (SubString(Col1,1,15):"|":SubString(Col2,16,Len(Col1)))

Probably if it does not works try using basic transformer in yours job