DSXchange

Hi Guys...


I am working on a job which requires me to output the max sequence number from a record based on a particular condition.

In my table i have columns which are code1, code2, code3,.......code36.
When ever there is '001' in the one of the codes i have to output the number associated with the code.
for example

code1|code2|code3|code4
12345|45678| 001

in this case i have to output 3 as 001 is in the code3.

Please advise me how to proceed as i am new to Datasatage....

Thanks

Would the final output for the record look like this?

12345|45678|3

Or would you only output 3?

Answer to this can make the solution easier :D

"Final delimited substring" technique.

the ouput should be only 3

"Count of delimited fields" technique.

Try this Dcount(InLink.Field["001",1,1], "|") too


Thanks,
Anupam

Anupam - what does the subfunction

InLink.Field["001",1,1]

do? This is not a legal substring expression.

ArndW wrote:Anupam - what does the subfunction InLink.Field["001",1,1] do? This is not a legal substring expression. ...

string [ delimiter, instance, fields ]



Thanks,
Anupam

Ahh, I see what you are trying to do; I thought you had misplaced the FIELD() parameters. Unfortunately the delimiter for both forms needs to be a single character, so this won't work.

Hi Guys

You totally misunderstood me.
I will try to rephrase the whole thing.
I have an Oracle table with 36 columns.(like code1, code2, code3,.....,code36)
So each record might have 36 codes. But there is an exception here , if 001 is found in any of the codes the remaining codes are NULLS.

So my requirement is to find the code number in the record which has 001.

For example if there is a 001 in code19 column, my output should be 19.

I hope you get it now.

Thanks

A series of 'If then else' checks, starting from the 'back'. Or check into the Oracle 'COALESE' function if you have that available, it may help.

ArndW wrote:Ahh, I see what you are trying to do; I thought you had misplaced the FIELD() parameters. Unfortunately the delimiter for both forms needs to be a single character, so this won't work. ...

I tried with "001" delimiter and it is working here ... so i dont think it should be single character...

Thanks,
Anupam

aakashmalu wrote:Hi Guys

You totally misunderstood me.
I will try to rephrase the whole thing.
I have an Oracle table with 36 columns.(like code1, code2, code3,.....,code36)
So each record might have 36 codes. But there is an exception here , if 001 is found in any of the codes the remaining codes are NULLS.

So my requirement is to find the code number in the record which has 001.

For example if there is a 001 in code19 column, my output should be 19.

I hope you get it now.

Thanks

Concadenate all column data into single string with "|" delimiter and try this Dcount(InLink.Field["001",1,1], "|") it will work...

Thanks,
Anupam