format string

[sainath]

Hi
sorry for confusion.routine worked fine.

Now my question is

i have input data

Code: Select all

key column    MSC txt
k1                  ABC---
                      DEF----
                      GHI---
k2                   123
like this i have 10 lines each has 132 char in length.

 now i want to insert  this in a table where each line inserted and line number is incremented by 1.

o/p 
is 
 key  column     lineno          MSC TXT

  k1                     1                ABC---
                           2                DEF---
                           3                 GH1---
  K2                      1                 123      and so on.

hope now it is clear to you .my problem is i dont how many lines i will have in i/p.
pl share your thought.
thks

[DSguru2B]

Looks like the only way to determine the start of a counter and end is the key. Which changes to null and then changes to some value. If this is consistent throughout your source file then you can achieve this by use of a single stage variable.

Code: Select all

Counter: If IsNull(in.key) = 1 or len(trim(in.key)) = 0 then Counter + 1 else 1

Specify the initial value of the stage variable 'Counter' as 0.
User Counter as the derivation of lineno column.

[sainath]

hi
thanks for your reply.i dont think it may work.I want to increment the counter(line no) if there is change in key column or if it is a next line.
how can i tell to datastage .share your thoughts.
thks

[DSguru2B]

Well there is a change in your key column. From your sample data, your key changes from k1 to null, again to null before it hits k2. My solution will work if your input data file is exactly like the way you have shown. If it is different then you need to give me the exact snapshot of your input file.
Counter getting incremented everytime there is a new line will not work as that will remain true for all the rows. How will you know when to initialize the counter back to 1 again.
Either you input file looks different than the sample data you have provided or i am going cukoo.'
Experts, some help here. Am i going cukoo

[chulett]

Either you input file looks different than the sample data you have provided or i am going cukoo.'

Or perhaps a little of both.

I'd ask Sai for two things. 1) Post a real example of the source data. 2) Actually try what you suggested. Saying "I don't think it may work" isn't helpful at all. Try it and then tell us why it didn't work. Or be pleasantly surprised when it does.

[DSguru2B]

Thank you Craig for the backup (not on the cukoo part ).
Sainath, if your input file is whatever you have shown in your original post, my solution will work. Otherwise as requested, we need the snapshot of the original input file.

[sainath]

Hi
Thanks for your reply.I am using same i/p data .when i tried to use your logic i am getting following error
Inserted value too large for column, row rejected.

Datastage is taking all lines at a time and trying to insert in 132 char
feild length in o/p.
but i want to take each line seperatly and insert in sepetate line by incrementing line number.
how should i approach.
I am thinking to use a special charac at the start of each new line
and when ever datastage reads i will force to insert in new line.
thanks for your help and advice.
sai