Re: RE: Fetching JDE data
Hi JDE_AMOL,
We are using Xe/AS400 and our requirement was similar. In some cases esp BLOB fields, the fields were exported as HEX chars. I experimented with the data and mapped the character to their ASC equivalents and in the excel sheet wrote a concat and lookup function to get it translated.
Later told my collegue to write a DB function to do the same to run from SQL. Worked with portions of BLOB fields. The table info and the lookup function is given below.
My requirement was to get Version name from blob field and was a limited one. This might be a crude method but works fine for us. May be of some use to you!
HEX ASCII
C1 A
C2 B
C3 C
C4 D
C5 E
C6 F
C7 G
C8 H
C9 I
D1 J
D2 K
D3 L
D4 M
D5 N
D6 O
D7 P
D8 Q
D9 R
E2 S
E3 T
E4 U
E5 V
E6 W
E7 X
E8 Y
E9 Z
F0 0
F1 1
F2 2
F3 3
F4 4
F5 5
F6 6
F7 7
F8 8
F9 9
00
CONCATENATE(VLOOKUP(MID(Q9,1,2),$R$9:$S$45,2),IF(MID(Q9,3,2) <>"00",VLOOKUP(MID(Q9,3,2),$R$9:$S$45,2),""),IF(MID(Q9,5,2) <> "00",VLOOKUP(MID(Q9,5,2),$R$9:$S$45,2),""),IF(MID(Q9,7,2) <> "00",VLOOKUP(MID(Q9,7,2),$R$9:$S$45,2),""),IF(MID(Q9,9,2) <> "00",VLOOKUP(MID(Q9,9,2),$R$9:$S$45,2),""),IF(MID(Q9,11,2)<> "00",VLOOKUP(MID(Q9,11,2),$R$9:$S$45,2),""),IF(MID(Q9,13,2)<>"00",VLOOKUP(MID(Q9,13,2),$R$9:$S$45,2),""),IF(MID(Q9,15,2)<>"00",VLOOKUP(MID(Q9,15,2),$R$9:$S$45,2),""),IF(MID(Q9,17,2)<>"00",VLOOKUP(MID(Q9,17,2),$R$9:$S$45,2),""),IF(MID(Q9,19,2)<>"00",VLOOKUP(MID(Q9,19,2),$R$9:$S$45,2),""))