How to find Max Sequence Number from a record
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 24
- Joined: Sun Nov 13, 2005 10:59 pm
How to find Max Sequence Number from a record
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
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
Re: How to find Max Sequence Number from a record
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
12345|45678|3
Or would you only output 3?
Answer to this can make the solution easier :D
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"Final delimited substring" technique.
Code: Select all
Field(InLink.Column, "|", Count(InLink.Column, "|") + 1, 1)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 24
- Joined: Sun Nov 13, 2005 10:59 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
"Count of delimited fields" technique.
Code: Select all
DCount(InLink.Field, "|")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Anupam - what does the subfunction
do? This is not a legal substring expression.InLink.Field["001",1,1]
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Charter Member
- Posts: 24
- Joined: Sun Nov 13, 2005 10:59 pm
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
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
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
I tried with "001" delimiter and it is working here ... so i dont think it should be single character...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. ...
Thanks,
Anupam
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.