Packed column from DB2/400

[chulett]

The only argument that should be quoted is the delimiter to be used.

[priyadarshikunal]

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 ''

[chulett]

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.

[antonyraj.deva]

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

[priyadarshikunal]

{if index(Col_D,'Col_E=',1) then field(Col_D[index(Col_D,'Col_E=',1) +6 ,99],'&',1) else 0}

Do you really understand what this logic trying to do?

[antonyraj.deva]

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

[antonyraj.deva]

Hi Priyadarshi,

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}

Do you really understand what this logic trying to do?

Truly I understood very little of the logic...

Thanks,
Tony

[priyadarshikunal]

{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"?

Thats what i thought.

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

[antonyraj.deva]

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

[chulett]

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.

[antonyraj.deva]

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

[chulett]

YES. It's just that you'll need two calls for each - first one as I noted then embed / inline another and tell it the result is "equal sign" delimited and take the second field.

[antonyraj.deva]

Hi Craig,

Thanks a lot for the inputs.

I'm bit confused on the final logic since I haven't worked before on embedded functions. Can you kindly show me how to make the result of first function to take "=" as delimiter and get the final output?

Thanks again,
Tony

[chulett]

Gack... I SO do not have time for this, trying to get out the door and to *my* job. Like a moth to a flame... still... quickly... untested!

Field(Field(DSLink5.Col_D,'&',2,1),"=",2,1) should get you ZZ

Etc.

[antonyraj.deva]

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