Fetching JDE data

[JDE_Amol]

Hi All,
I am fetching data from JD Edwards World database's tables but we are facing some problems. I am CLI APIs but it return me HEX value for EBCDIC character anybody has any idea about this. I am facing this problem only with JDE tables other AS/400's collection tables return me data in proper way. Please provide me guidelines in this connection.

 

[Colin_Hugill]

Whatever you are doing with API's is nominally beyond the scope of this
list which is directed at JDE software. There are no reasons I can
think of that would cause JDE data tables (files) to return data in any
format different to any other data table (file) on the AS/400 (iSeries)
other than "operator" error.



Time you did some serious analysis of your work against the JDE tables
in comparison with what you have done against other tables and also time
you commenced some self study activity of technical manuals relevant to
what you are doing.



Let me re-emphasise that you should:



1. Use the search function in JDEList to check what has already been
posted about a topic.

2. Read the four "permanent" posts at the start of the "JDEdwards World"
list from ekempter and Christian Audet

3. Read the document in the link from ekempter's post titled "How to ask
Questions the Smart Way"

 

[JDE_Amol]

Re: RE: Fetching JDE data

Hi Colin,
Thanks for your reply i checked all the old post queries but i did not get any query in this regard. If you have JD Edwards installed at your end could you please check one thing use IBM iseries navigator and fetch data from any JDE table. Because my programe and this iseries navigator which are running on windows machine both are returning the same HEX data.

 

[ginny]

HELLO AMOL,
I STARTED WORKING IN WORLD RECENTLY,R U GETTING THE DATA INTO EXCEL? IF U R, I THINK I CAN HELP U ABOUT IT.

 

[Hal_McGee]

Re: RE: Fetching JDE data

In iSeries navigator when you set up the ODBC connections there is a tab
for converting EBDIC codes. Go find that radio box and check for
translations.
Good Luck

Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC



JDE_Amol
<amol.ghatge@gmai
l.com> To
Sent by: [email protected]
jdeworld-bounces@ cc
jdelist.com
Subject
Re: RE: Fetching JDE data
01/19/2006 12:09
PM


Please respond to
JD Edwards® World
<jdeworld@jdelist
.com>






Hi Colin,
Thanks for your reply i checked all the old post queries but i did not get
any query in this regard. If you have JD Edwards installed at your end
could you please check one thing use IBM iseries navigator and fetch data
from any JDE table. Because my programe and this iseries navigator which
are running on windows machine both are returning the same HEX data.

 

[G_Smith]

Generally when Hex data is returned in a query, either the properties of the Client Access download has not had the "Convert CCSID 65535" option selected. Or if using ODBC, in the Translation Tab there is an option to "Convert CCSID 65535" to be selected. Once selected in ODBC the option usually remains for the specific DSN, but everytime a download is done using Client Access the properties have to be set. Not sure about the Operations Navigator though.

Hope this helps.

 

[JDE_Amol]

Hi Smith
My problem is resolved now it is working soomthly Actually JD Edwards sets CCSID flag for character field to 65535 that means does not perform transformation while fetching data cross paltform i worte SQL query with cast operator and changed CCSID value to 37 means US english now my code is fetching data properly.

 

[vijayendra]

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),""))

 

[Karthikeyan_pass]

Generally when Hex data is returned in a query, either the properties of the Client Access download has not had the "Convert CCSID 65535" option selected. Or if using ODBC, in the Translation Tab there is an option to "Convert CCSID 65535" to be selected. Once selected in ODBC the option usually remains for the specific DSN, but everytime a download is done using Client Access the properties have to be set. Not sure about the Operations Navigator though.

Hope this helps.

This is worked greatly