Packed column from DB2/400

[ray.wurlod]

This is the first mention that a column may not be present, and adds hugely to the complexity of the solution.

For each field you will first need to determine existence in the string, then find the delimiters (more specifically the character positions thereof) to the left and right of this position in the string, and extract the intervening characters.

That's a specification in English; try to implement this as an expression and let us know how you fared.

[antonyraj.deva]

Hi Ray,

Thanks a lot for the reply. Actually I'd mentioned the column missing factor and the complexity in the first post.

Col A,Col B,Col C,Col D
1234,ab2f,pazz,"&Col E=ZZ&Col F= &Col G=5478&Col H=100.00
2345,cd3f,ddzz,"&Col F=IN&Col G=9801&Col H=250.25&Col J=20060630

2. The '&' seperated columns are not following a uniform pattern i.e., it varies from record to record (some start with Col E and some with Col X etc).
3. This field contains around 300 '&' seperated columns in all possible combinations and we have 1 million records per monthly extract.

Honestly I have no clue on how to translate the logic in to an expression

Please correct me if I am wrong in the following logic.

"if index(DSLink5.Col_D,'Col_E=',1) >0 then Field(Field(DSLink5.Col_D, '&',2,1),"=",2,1) else 0"

Thanks in advance,
Tony

[priyadarshikunal]

I took the same value as you provided in the example

and put my derivation for different columns like :
input

"&Col E=ZZ&Col F= &Col G=5478&Col H=100.00

COL_E=
if index(DSLink5.COL_D,'Col E=',1)>0 then field(DSLink5.COL_D[index(DSLink5.COL_D,'Col E=',1) +6 ,99],'&',1) else 0

COL_F=
if index(DSLink5.COL_D,'Col F=',1)>0 then field(DSLink5.COL_D[index(DSLink5.COL_D,'Col F=',1) +6 ,99],'&',1) else 0

COL_G=
if index(DSLink5.COL_D,'Col G=',1)>0 then field(DSLink5.COL_D[index(DSLink5.COL_D,'Col G=',1) +6 ,99],'&',1) else 0

COL_H=
if index(DSLink5.COL_D,'Col H=',1)>0 then field(DSLink5.COL_D[index(DSLink5.COL_D,'Col H=',1) +6 ,99],'&',1) else 0

and the ouput i got was :

Code: Select all

COL_E:ZZ COL_F:  COL_G:5478 COL_H:100.00

I suggest you to check your derivation first. And for your informantion length of 'Col E=' is 6 not 5

[antonyraj.deva]

Hi Priyadarshi,

Thanks a lot for all the help. The output is perfect in the sample data.

The only thing which remains is that when I use the logic in real data, all the output rows are zeros.

The actual variable name is "Z10HFC" and the derivation I've used is

Code: Select all

If Index(DSLink1.SDFTSW,'Z10HFC=',1) >0 Then Field(DSLink1.SDFTSW[ Index(DSLink1.SDFTSW,'Z10HFC=',1)+7,99],"&",1) Else 0 

I'd given +7 because the length of the variable till the "=" is 7 characters.

Kindly advise what I'm missing out here...
Thanks,
Tony

[agpt]

Tony,

I think you should use +6

[antonyraj.deva]

Tony,

I think you should use +6

Hi AGPT,

The logic used is length of the variable till the "=" sign which in this case is 7 (Z10HFC=).

Thanks,
Tony

[antonyraj.deva]

Hi All,

The logic was not working in the real data since there was an error in the source string itself. Now the results are perfect as desired.

Thanks a million to everyone for helping in this issue.

Special thanks to Priyadarshi, Battaliou, Craig & Ray. :D

I'm marking the topic as resolved.

-Tony

[chulett]

Thank goodness.