Packed column from DB2/400
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
and why you need double quotes around the column name?
and have to tried to get what battaliou wrote in the post? which needs to be changed a bit because you already read the column value and you need to parse.
if index(input.col,'COL E=',1) > 0 then field(input.col[index(input.col,'COL E=',1)+6,99],'&',1) else ''
and have to tried to get what battaliou wrote in the post? which needs to be changed a bit because you already read the column value and you need to parse.
if index(input.col,'COL E=',1) > 0 then field(input.col[index(input.col,'COL E=',1)+6,99],'&',1) else ''
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
You don't even need the ">0" part.
ps. In no way take this as an endorsement of what was posted, just making a point regarding syntax.
ps. In no way take this as an endorsement of what was posted, just making a point regarding syntax.
Last edited by chulett on Thu May 20, 2010 5:39 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Hi All,
After removing the quote around the column name (Field(Col_D,"&",2)) and running the job the output returned was "Co" (the first two letters after the delimiter) for all the rows.
While trying to impart the index logic {if index(Col_D,'Col_E=',1) then field(Col_D[index(Col_D,'Col_E=',1) +6 ,99],'&',1) else 0} the output is zeros for all the fields.
Thanks in advance,
Tony
After removing the quote around the column name (Field(Col_D,"&",2)) and running the job the output returned was "Co" (the first two letters after the delimiter) for all the rows.
While trying to impart the index logic {if index(Col_D,'Col_E=',1) then field(Col_D[index(Col_D,'Col_E=',1) +6 ,99],'&',1) else 0} the output is zeros for all the fields.
Thanks in advance,
Tony
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Hi Priyadarshi,
{if index(Col_D,'Col_E=',1) then field(Col_D[index(Col_D,'Col_E=',1) +6 ,99],'&',1) else 0}
In the above logic what exactly is the use of aruguments "+6" and "99"?
I guess if I can understand these arguments and give appropriate values for them then the issue may be resolved.
Thanks in advance,
Tony
{if index(Col_D,'Col_E=',1) then field(Col_D[index(Col_D,'Col_E=',1) +6 ,99],'&',1) else 0}
In the above logic what exactly is the use of aruguments "+6" and "99"?
I guess if I can understand these arguments and give appropriate values for them then the issue may be resolved.
Thanks in advance,
Tony
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Thats what i thought.antonyraj.deva wrote:{if index(Col_D,'Col_E=',1) then field(Col_D[index(Col_D,'Col_E=',1) +6 ,99],'&',1) else 0}
In the above logic what exactly is the use of aruguments "+6" and "99"?
index(Col_D,'Col_E=',1) is trying to find 'Col_E=' in the string which from example you don't have. you have 'Col E=' (without underscore). Hence you are getting value specified in else.
field(Col_D[index(Col_D,'Col_E=',1) +6 ,99],'&',1)
index(Col_D,'Col_E=',1) gives the starting position of the string. once again not to mention, you should use 'Col E=' (without underscore).
Then +6 gives you position of character after '=' as the length of 'Col E=' is 6. '[<index+6>,99]' gives you 99 characters after '=' assuming the maximum length of value will be 99. and then taking the value before '&' using field to get the value you need.
Example:
Col E=ZZ&Col F=YY
index() in if condition will return 1 then +6 will return 7.
99 character long substring from character at pos 7 will be ZZ&Col F=YY. And hence the field() will return ZZ
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Hi Priyadarshi,
I changed the Col_E to Col E and then ran the job. The derivation is
if index(DSLink5.Col D,'Col E=',1) then field(DSLink5.Col D [index(DSLink5.Col D,'Col E=',1) +5,99],'&',1) else 0 Still the result is zeros for all the rows.
I've kept "+5" because the length of 'Col E=' is 5 and data is at 6th position. Also the maximum length of the data is only 3. So will specifying the maximum possible length as 99 have any impact on the output?
Kindly correct me if have got the logic wrongly.
Thanks,
Tony
I changed the Col_E to Col E and then ran the job. The derivation is
if index(DSLink5.Col D,'Col E=',1) then field(DSLink5.Col D [index(DSLink5.Col D,'Col E=',1) +5,99],'&',1) else 0 Still the result is zeros for all the rows.
I've kept "+5" because the length of 'Col E=' is 5 and data is at 6th position. Also the maximum length of the data is only 3. So will specifying the maximum possible length as 99 have any impact on the output?
Kindly correct me if have got the logic wrongly.
Thanks,
Tony
Column names do not support spaces. What the heck 8.x release are you on? What fixpacks are installed? You shouldn't have to be going through any kind of Index shenanigans like that. [shudder]
I'm assuming your Col_D value actually is what you've said it is, quote as data and all. One example from your initial post:
"&Col E=ZZ&Col F= &Col G=5478&Col H=100.00
Now, the Field() function should work just fine on this and there are five delimited strings it should pull:
Field(DSLink5.Col_D,'&',1,1) should get you "
Field(DSLink5.Col_D,'&',2,1) should get you Col E=ZZ
Field(DSLink5.Col_D,'&',3,1) should get you Col F=
Field(DSLink5.Col_D,'&',4,1) should get you Col G=5478
Field(DSLink5.Col_D,'&',5,1) should get you Col H=100.00
Field(DSLink5.Col_D,'&',6,1) should get you null (etc)
If that's not what happening, then either you haven't told us the whole story or you are on some 8.0.x release and thus the victim of an unfortunate bug.
I'm assuming your Col_D value actually is what you've said it is, quote as data and all. One example from your initial post:
"&Col E=ZZ&Col F= &Col G=5478&Col H=100.00
Now, the Field() function should work just fine on this and there are five delimited strings it should pull:
Field(DSLink5.Col_D,'&',1,1) should get you "
Field(DSLink5.Col_D,'&',2,1) should get you Col E=ZZ
Field(DSLink5.Col_D,'&',3,1) should get you Col F=
Field(DSLink5.Col_D,'&',4,1) should get you Col G=5478
Field(DSLink5.Col_D,'&',5,1) should get you Col H=100.00
Field(DSLink5.Col_D,'&',6,1) should get you null (etc)
If that's not what happening, then either you haven't told us the whole story or you are on some 8.0.x release and thus the victim of an unfortunate bug.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Hi Craig,
The version we are using in this project is 8.1.1 with no fix packs.
Actually the original column names doesn't have spaces. In the sample data file I'd created the columns have underscores only. Sorry for being unclear regarding the same.
The approach I'm trying right now as suggested by Ray is to create 300 stage variables (Col_E, Col_F etc) and trying to fetch only the data ie., "ZZ" for stage variable Col_E, " " for Col_F etc.
When I try to view the source data the quote character is not visible but I know it is present in the source file.
Please advise if Field() function will work for the desired output.
Thanks,
Tony
The version we are using in this project is 8.1.1 with no fix packs.
Actually the original column names doesn't have spaces. In the sample data file I'd created the columns have underscores only. Sorry for being unclear regarding the same.
The approach I'm trying right now as suggested by Ray is to create 300 stage variables (Col_E, Col_F etc) and trying to fetch only the data ie., "ZZ" for stage variable Col_E, " " for Col_F etc.
When I try to view the source data the quote character is not visible but I know it is present in the source file.
Please advise if Field() function will work for the desired output.
Thanks,
Tony
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
-
- Premium Member
- Posts: 138
- Joined: Wed Jul 16, 2008 9:51 pm
- Location: Kolkata
Thanks a million Craig for your support. Now the issue is partially resolved. :D
I'm able to get value "ZZ" wherever Col_D contains Col_E as the first variable between the delimiter.
The part which is still unsolved is that if Col_E is not present (&Col_F=IN&Col_G=9801&Col_H=250.25&Col_J=20060630&)the output should be zero.
But when I use the logic "Field(Field(DSLink5.Col_D,'&',2,1),"=",2,1)" the output got is "IN" which should be the output of the stage variable Col_F.
Thanks in advance to all....,
Tony
I'm able to get value "ZZ" wherever Col_D contains Col_E as the first variable between the delimiter.
The part which is still unsolved is that if Col_E is not present (&Col_F=IN&Col_G=9801&Col_H=250.25&Col_J=20060630&)the output should be zero.
But when I use the logic "Field(Field(DSLink5.Col_D,'&',2,1),"=",2,1)" the output got is "IN" which should be the output of the stage variable Col_F.
Thanks in advance to all....,
Tony