How to find Max Sequence Number from a record

[aakashmalu]

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

[tcj]

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

[ray.wurlod]

"Final delimited substring" technique.

Code: Select all

Field(InLink.Column, "|", Count(InLink.Column, "|") + 1, 1)

[aakashmalu]

the ouput should be only 3

[ray.wurlod]

"Count of delimited fields" technique.

Code: Select all

DCount(InLink.Field, "|")

[sb_akarmarkar]

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


Thanks,
Anupam

[ArndW]

Anupam - what does the subfunction

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

do? This is not a legal substring expression.

[sb_akarmarkar]

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

string [ delimiter, instance, fields ]



Thanks,
Anupam

[ArndW]

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.

[aakashmalu]

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

[chulett]

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.

[sb_akarmarkar]

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

[sb_akarmarkar]

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

[ray.wurlod]

Code: Select all

FUNCTION FindIt(String, SearchString, Delimiter)
$OPTIONS INFO.LOCATE

* Make a copy of the string
TestString = String

* Convert delimiter into dynamic array delimiter
Convert Delimiter To @FM In TestString

* Search for search string returning field position
Locate SearchString In TestString Setting Position
Then
   Ans = Position
End
Else
   Ans = 0  ; * not found
End

RETURN(Ans)