Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Converting a "nonconvertible hexidecimal column" to English from an AS400 DB

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Converting a "nonconvertible hexidecimal column" to English from an AS400 DB
 Login/Join
 
Gold member
posted
I tried searching and had no luck.

Has anyone ever done any "casting" to convert a nonconvertible hexidecimal field to english from an AS400 DB in Webfocus?

I am accessing a AS400 DB with an ODBC connection and the data is all garbled because the Hex values are not converted.

Can I do something in my MFD to convert them or does it have to be done at the procedure level?

This has been a nightmare getting this data into Webfocus, any help is appreciated.

This message has been edited. Last edited by: Kerry,


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Virtuoso
posted Hide Post
Rob

what do you mean by "nonconvertible hexidecimal"

When I search with google I only find some very old hits (1997??).

Have you asked this at Information Builders




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
quote:
Originally posted by FrankDutch:
Rob

what do you mean by "nonconvertible hexidecimal"

When I search with google I only find some very old hits (1997??).

Have you asked this at Information Builders


"non-convertible hexidecimal" is the formatting of the data from the AS400 is what I was told. When you bring the data in through the ODBC connection in WF you get garbled data like 04040404040.

The system engineer I spoke with said there is something called data casting which converts the hex values to english. Here is some code he gave me that he is using.

SELECT

TRIM(CAST(F01.GLMCU as varchar(100) CCSID 37)) as "PACMAN_Project_Code_Location"

, TRIM(CAST(F01.GLTORG as varchar(100) CCSID 37)) as "PACMAN_INVC_Transaction_Orig"

, TRIM(CAST(F01.GLSUB as varchar(100) CCSID 37)) as "PACMAN_INVC_CSI_Code"

, F01.GLAN8 as "PACMAN_INVC_Vendor_Number"

, F01.GLDGJ as "PACMAN_INVC_Julian_GL_Date"

, TRIM(CAST(F01.GLDCT as varchar(100) CCSID 37)) as "PACMAN_INVC_Document_Type"

, F01.GLDOC as "PACMAN_INVC_Document_Number"

, ((F01.GLAA) / 100) as "PACMAN_INVC_Amount"

, TRIM(CAST(F01.GLVINV as varchar(100) CCSID 37)) as "PACMAN_INVC_Invoice_Number"

, TRIM(CAST(F01.GLPO as varchar(100) CCSID 37)) as "PACMAN_INVC_PO_Number"

, TRIM(CAST(F01.GLDCTO as varchar(100) CCSID 37)) as "PACMAN_INVC_PO_Document_Type"

, TRIM(CAST(F01.GLSBL as varchar(100) CCSID 37)) as "PACMAN_INVC_Expense_Center"

FROM

PRODDATA.F0911 F01


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Gold member
posted Hide Post
Anyone else every have success pulling in AS400 data into Webfocus, with this hex format conversion issue?

I was just reading up a little on SQL Casting and the author of this article here: http://www.itjungle.com/mpo/mpo091103-story03.html mentions J.D. Edwards as being a problem child to work with data.

Well wouldn't ya know it, the program I am trying to get this data from is a J.D. Edwards program. Grrrrr.

Thoughts?


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Expert
posted Hide Post
What DBMS are you talking about, Oracle, DB2? As far as my knowledge goes, "AS400" is a computer platform and not a DBMS.

I have never had to do any conversion when retrieving data from an Oracle or DB2 table that's on an AS400 server.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Here is a shot from my MFD. It is an ODBC connection.



Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Expert
posted Hide Post
Unfortunately, I am not too familiar with ODBC connections, hopefully someone else will be able to assist.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
How about tech support at IBI.

http://techsupport.informationbuilders.com/contactus.jsp

1-212-736-6130

This is very specific, so maybe they know the answer.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
I'm not a jde person, but still I have just some random thoughts on this issue.

What type of ODBC connection is it?
Is it a specific one for JDE (like ODA or jdeDirect for instance) or is it a generic one?
Can you configure the driver in any way?
Does it maybe have options for data conversions or something like that that would solve your problem?


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Master
posted Hide Post
Your ODBC connector should not make any difference. JDE is basically a DB2 database. I think you are dealing with packed fields. You need to look carefully at your actual and your usage. Can you post your MFD and point out which fields are giving you a problem.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
quote:
Originally posted by PBrightwell:
Your ODBC connector should not make any difference. JDE is basically a DB2 database. I think you are dealing with packed fields. You need to look carefully at your actual and your usage. Can you post your MFD and point out which fields are giving you a problem.


My MFD is below. All the usage and actuals have not been modified yet, this is how they came in when the MFD was set up.


FILENAME=F0911T, SUFFIX=SQLODBC , $
SEGMENT=F0911T, SEGTYPE=S0, $
FIELDNAME=GLDCT, ALIAS=GLDCT, USAGE=A2, ACTUAL=A2,
MISSING=ON, $
FIELDNAME=GLDOC, ALIAS=GLDOC, USAGE=P10, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=GLKCO, ALIAS=GLKCO, USAGE=A5, ACTUAL=A5,
MISSING=ON, $
FIELDNAME=GLDGJ, ALIAS=GLDGJ, USAGE=P8, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=GLLT, ALIAS=GLLT, USAGE=A2, ACTUAL=A2,
MISSING=ON, $
FIELDNAME=GLJELN, ALIAS=GLJELN, USAGE=P10.1, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=GLEXTL, ALIAS=GLEXTL, USAGE=A2, ACTUAL=A2,
MISSING=ON, $
FIELDNAME=GLPM01, ALIAS=GLPM01, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM02, ALIAS=GLPM02, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM03, ALIAS=GLPM03, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM04, ALIAS=GLPM04, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM05, ALIAS=GLPM05, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM06, ALIAS=GLPM06, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM07, ALIAS=GLPM07, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM08, ALIAS=GLPM08, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM09, ALIAS=GLPM09, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLPM10, ALIAS=GLPM10, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLABT1, ALIAS=GLABT1, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLABR1, ALIAS=GLABR1, USAGE=A12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=GLABT2, ALIAS=GLABT2, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLABR2, ALIAS=GLABR2, USAGE=A12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=GLABT3, ALIAS=GLABT3, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLABR3, ALIAS=GLABR3, USAGE=A12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=GLABT4, ALIAS=GLABT4, USAGE=A1, ACTUAL=A1,
MISSING=ON, $
FIELDNAME=GLABR4, ALIAS=GLABR4, USAGE=A12, ACTUAL=A12,
MISSING=ON, $
FIELDNAME=GLITM, ALIAS=GLITM, USAGE=P10, ACTUAL=P8,
MISSING=ON, $


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Expert
posted Hide Post
Rob,

Have you done an Advanced Search on JD Edwards? Somewhere in my small brain is a faint memory from my IBI days from the little that I worked with AS400. I seem to remember some post processing necessary on the master.

I may be all wet but why don't you search the IBI web site and/or open a case.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Master
posted Hide Post
quote:
FIELDNAME=GLDOC, ALIAS=GLDOC, USAGE=P10, ACTUAL=P8,


This may be your problem. These fields are actually packed. An ACTUAL of P8 can hold a number that is 15 characters plus a sign. Try changing your USAGE to P15.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
If it is just the packed data that is giving you trouble, maybe you could define them all as alphas. I think that would prevent any attempt to convert types by any of the processes involved.

For example,
FIELDNAME=GLDOC, ALIAS=GLDOC, USAGE=P10, ACTUAL=P8,
becomes
FIELDNAME=GLDOC, ALIAS=GLDOC, USAGE=A8, ACTUAL=A8,

Then, you could apply the UFMT function to convert the alpha data to hexadecimal--
HEXGLDOC/A16 = UFMT(GLDOC, 8, HELGLDOC)

...and somewhere out there must be a function that will convert a hex string to a number


Jeff
WebFOCUS 8.0.09, Unix-Win-z/OS
FOCUS 7.3.1 on z/OS
 
Posts: 34 | Location: Minneapolis, MN | Registered: June 10, 2003Report This Post
Gold member
posted Hide Post
quote:
Originally posted by GinnyJakes:
Rob,

Have you done an Advanced Search on JD Edwards? Somewhere in my small brain is a faint memory from my IBI days from the little that I worked with AS400. I seem to remember some post processing necessary on the master.

I may be all wet but why don't you search the IBI web site and/or open a case.


What is the URL for IBI that you are referring to? And how would I open a case? Is that free?


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Virtuoso
posted Hide Post
The support site can be reached at http://techsupport.informationbuilders.com

To be able to search for answers and/or register and update a call you'll need to log on. Creating a userid is free, if you are a customer of ibi. For creating your userid you need to know your sitecode (something like 1234.56) and the exact name of your company as registered by ibi.
Use of the site is free of charge.

Hope this helps ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
Hi Rob,

There are JDE World and Enterprise One adapters that take care of all this for you. It leverages JDE security and enhances your synonym (.mas) by adding friendly business descriptions to your metadata. It also takes care of all the necessary conversions:

* date conversions (julian to gregorian)
* decimal precision
* UDC lookups
* presumptive joins (JDE World only)

If you are interested, contact your local IBI rep.

Regards,
Richard
 
Posts: 8 | Location: Corporate | Registered: October 07, 2006Report This Post
Master
posted Hide Post
You can see what Richard is talking about with the "friendly business descriptions", however, JDE is a strange animal. I took over for someone else and have discovered that she normally divides her numbers by 10 to whatever power will give her the correct number of decimal places. It took me 2 days to figure out that a problem that she thought was a zero divide was actually an overflow. A field defined as USAGE=P15.5C, ACTUAL=P8.0 was aligning the decimal places and not with the correct 5 positions. In the program she divided this field by 100000. When I changed the MFD to USAGE=P15 I resolved the issue.

My F0911 looks like this:
$ DATE JDE CONVERSION PERFORMED: Fri Jun 18 12:57:40 1999
$
$ FILE DESCRIPTION: Account Ledger
$ DATE MASTER CREATED: Fri Jun 18 12:54:27 1999
$
FILE=F0911, SUFFIX=SQL400,$
SEGNAME=F0911, SEGTYPE=S0,$
FIELD=DOCUMENT_COMPANY,
ALIAS='GLKCO', USAGE=A5, ACTUAL=A5,
DESCRIPTION='Document Company',
TITLE=' Doc, Co',$
FIELD=DOCUMENT_TYPE_CODE ,
ALIAS='GLDCT', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Document Type',
TITLE='Do,Ty',$
FIELD=DOCUMENT_NUMBER,
ALIAS='GLDOC', USAGE=P9S , ACTUAL=P8,
DESCRIPTION='Document Number',
TITLE='Document, Number',$
FIELD=G_L_DATE,
ALIAS='GLDGJ', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='G_L Date',
TITLE=' G/L, Date',$
FIELD=JOURNAL_ENTRY_LINE_NUMBER,
ALIAS='GLJELN', USAGE=P9.1S, ACTUAL=P7,
DESCRIPTION='Journal Entry Line Number',
TITLE='JE Line,Number',$
FIELD=LINE_EXTENSION_CODE,
ALIAS='GLEXTL', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Line Extension Code',
TITLE=' Line,Extension',$
FIELD=G_L_POSTED_CODE,
ALIAS='GLPOST', USAGE=A1, ACTUAL=A1,
DESCRIPTION='G_L Posted Code',
TITLE='P,C',$
FIELD=BATCH_NUMBER,
ALIAS='GLICU', USAGE=P9S , ACTUAL=P8,
DESCRIPTION='Batch Number',
TITLE=' Batch, Number',$
FIELD=BATCH_TYPE_CODE ,
ALIAS='GLICUT', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Batch Type',
TITLE='Bth,Ty',$
FIELD=BATCH_DATE,
ALIAS='GLDICJ', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Batch Date',
TITLE=' Batch, Date',$
FIELD=BATCH_SYSTEM_DATE,
ALIAS='GLDSYJ', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Batch System Date',
TITLE='ICU Sys, Date',$
FIELD=BATCH_TIME,
ALIAS='GLTICU', USAGE=P7 , ACTUAL=P6,
DESCRIPTION='Batch Time',
TITLE=' Batch, Time',$
FIELD=COMPANY,
ALIAS='GLCO', USAGE=A5, ACTUAL=A5,
DESCRIPTION='Company',
TITLE=' Co',$
FIELD=ACCOUNT_NUMBER,
ALIAS='GLANI', USAGE=A29, ACTUAL=A29,
DESCRIPTION='Account Number',
TITLE=' Account Number',$
FIELD=ACCOUNT_MODE,
ALIAS='GLAM', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Account Mode',
TITLE='A,M',$
$ ACCEPT='1' OR '2' OR '3',$
FIELD=ACCOUNT_ID,
ALIAS='GLAID', USAGE=A8, ACTUAL=A8,
DESCRIPTION='Account ID',
TITLE='Account, ID',$
FIELD=BUSINESS_UNIT,
ALIAS='GLMCU', USAGE=A12, ACTUAL=A12,
DESCRIPTION='Business Unit',
TITLE=' Business, Unit',$
FIELD=OBJECT_ACCOUNT,
ALIAS='GLOBJ', USAGE=A6, ACTUAL=A6,
DESCRIPTION='Object Account',
TITLE=' Obj, Acct',$
FIELD=SUBSIDIARY,
ALIAS='GLSUB', USAGE=A8, ACTUAL=A8,
DESCRIPTION='Subsidiary',
TITLE=' Sub',$
FIELD=SUBLEDGER,
ALIAS='GLSBL', USAGE=A8, ACTUAL=A8,
DESCRIPTION='Subledger',
TITLE=' Sub, ledger',$
FIELD=SUBLEDGER_TYPE_CODE ,
ALIAS='GLSBLT', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Subledger Type',
TITLE='Sub,Type',$
FIELD=LEDGER_TYPE_CODE ,
ALIAS='GLLT', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Ledger Type',
TITLE='LT',$
FIELD=G_L_PERIOD_NUMBER,
ALIAS='GLPN', USAGE=P3S , ACTUAL=P2,
DESCRIPTION='G_L Period Number',
TITLE='Per,No',$
FIELD=CENTURY,
ALIAS='GLCTRY', USAGE=P3S , ACTUAL=P2,
DESCRIPTION='Century',
TITLE='Century',$
$ ACCEPT=19 TO 20,$
FIELD=FISCAL_YEAR,
ALIAS='GLFY', USAGE=P3S , ACTUAL=P2,
DESCRIPTION='Fiscal Year',
TITLE='FY',$
FIELD=FISCAL_QUARTER__OBSOLETE,
ALIAS='GLFQ', USAGE=A4, ACTUAL=A4,
DESCRIPTION='Fiscal Quarter (Obsolete',
TITLE='Fisc,Qtr',$
$ ACCEPT=' ' OR ' ' OR ' ' OR ' ' OR '1',$
FIELD=CURRENCY_CODE,
ALIAS='GLCRCD', USAGE=A3, ACTUAL=A3,
DESCRIPTION='Currency Code',
TITLE='Cur,Cod',$
FIELD=EXCHANGE_RATE,
ALIAS='GLCRR', USAGE=P15.7S, ACTUAL=P8,
DESCRIPTION='Exchange Rate',
TITLE=' Exchange, Rate',$
FIELD=HISTORICAL_EXCHANGE_RATE,
ALIAS='GLHCRR', USAGE=P15.7S, ACTUAL=P8,
DESCRIPTION='Historical Exchange Rate',
TITLE=' Historical, Rate',$
FIELD=HISTORICAL_DATE,
ALIAS='GLHDGJ', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Historical Date',
TITLE='Historical, Date',$
FIELD=AMOUNT,
ALIAS='GLAA', USAGE=P15.2NS , ACTUAL=P8.0,
DESCRIPTION='Amount',
TITLE=' Amount',$
FIELD=UNITS,
ALIAS='GLU', USAGE=P15.2CS , ACTUAL=P8.0,
DESCRIPTION='Units',
TITLE=' Units',$
FIELD=UNIT_OF_MEASURE_CODE ,
ALIAS='GLUM', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Unit of Measure',
TITLE='UM',$
FIELD=G_L_OFFSET,
ALIAS='GLGLC', USAGE=A4, ACTUAL=A4,
DESCRIPTION='G_L Offset',
TITLE=' G/L,Offset',$
FIELD=REVERSE_OR_VOID__R_V,
ALIAS='GLRE', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Reverse or Void (R/V',
TITLE='R,V',$
$ ACCEPT='R' OR 'V' OR ' ',$
FIELD=EXPLANATION___NAME_ALPHA,
ALIAS='GLEXA', USAGE=A30, ACTUAL=A30,
DESCRIPTION='Explanation - Name Alpha',
TITLE=' Explanation, Alpha Name',$
FIELD=EXPLANATION___REMARK,
ALIAS='GLEXR', USAGE=A30, ACTUAL=A30,
DESCRIPTION='Explanation - Remark',
TITLE=' Explanation, Remark',$
FIELD=REFERENCE_1___JE__VOUCHER__INVOICE__ETC,
ALIAS='GLR1', USAGE=A8, ACTUAL=A8,
DESCRIPTION='Reference 1 - JE, Voucher, Invoice, etc',
TITLE='Reference, 1',$
FIELD=REFERENCE_2___ADDRESS_NUMBER,
ALIAS='GLR2', USAGE=A8, ACTUAL=A8,
DESCRIPTION='Reference 2 - Address Number',
TITLE='Reference, 2',$
FIELD=REFERENCE_3___ACCOUNT_RECONCILIATION,
ALIAS='GLR3', USAGE=A8, ACTUAL=A8,
DESCRIPTION='Reference 3 - Account Reconciliation',
TITLE='Reference, 3',$
FIELD=PAY_ITEM,
ALIAS='GLSFX', USAGE=A3, ACTUAL=A3,
DESCRIPTION='Pay Item',
TITLE='Pay,Itm',$
FIELD=ORIGINAL_DOCUMENT_NO,
ALIAS='GLODOC', USAGE=P9S , ACTUAL=P8,
DESCRIPTION='Original Document No',
TITLE='Original,Document',$
FIELD=ORIGINAL_DOCUMENT_TYPE_CODE ,
ALIAS='GLODCT', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Original Document Type',
TITLE='Orig,Do Ty',$
FIELD=ORIGINAL_PAY_ITEM,
ALIAS='GLOSFX', USAGE=A3, ACTUAL=A3,
DESCRIPTION='Original Pay Item',
TITLE=' Orig,PayItm',$
FIELD=PURCHASE_ORDER_DOCUMENT_COMPANY,
ALIAS='GLPKCO', USAGE=A5, ACTUAL=A5,
DESCRIPTION='Purchase Order Document Company',
TITLE=' Doc, Co',$
FIELD=ORIGINAL_ORDER_DOCUMENT_COMPANY,
ALIAS='GLOKCO', USAGE=A5, ACTUAL=A5,
DESCRIPTION='Original Order Document Company',
TITLE=' Doc, Co',$
FIELD=PURCHASE_ORDER_DOCUMENT_TYPE_CODE ,
ALIAS='GLPDCT', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Purchase Order Document Type',
TITLE=' PO,Do Ty',$
FIELD=ADDRESS_NUMBER,
ALIAS='GLAN8', USAGE=P9S , ACTUAL=P8,
DESCRIPTION='Address Number',
TITLE='Address, Number',$
FIELD=PAYMENT_NUMBER,
ALIAS='GLCN', USAGE=A8, ACTUAL=A8,
DESCRIPTION='Payment Number',
TITLE='Payment, Number',$
FIELD=CHECK_DATE,
ALIAS='GLDKJ', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Check Date',
TITLE=' Check, Date',$
FIELD=CHECK_CLEARED_DATE,
ALIAS='GLDKC', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Check Cleared Date',
TITLE=' Check,Cleared',$
FIELD=SERIAL_NUMBER,
ALIAS='GLASID', USAGE=A25, ACTUAL=A25,
DESCRIPTION='Serial Number',
TITLE=' Serial, Number',$
FIELD=BATCH_REAR_END_POSTED_CODE,
ALIAS='GLBRE', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Batch Rear End Posted Code',
TITLE='BRE,Post',$
$ ACCEPT=' ' OR '*' OR 'P',$
FIELD=RECONCILED_CODE ,
ALIAS='GLRCND', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Reconciled',
TITLE='R,ND',$
FIELD=SUMMARIZED_CODE,
ALIAS='GLSUMM', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Summarized Code',
TITLE='S,C',$
FIELD=PURGE_CODE,
ALIAS='GLPRGE', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Purge Code',
TITLE='P,C',$
FIELD=FLAG_FOR_1099,
ALIAS='GLTNN', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Flag for 1099',
TITLE='1099,Flag',$
$ ACCEPT=' ' OR '0' OR '1',$
FIELD=ALTERNATE_G_L_POSTING_CODE_1,
ALIAS='GLALT1', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Alternate G/L Posting Code 1',
TITLE='P,C',$
FIELD=ALTERNATE_G_L_POSTING_CODE_2,
ALIAS='GLALT2', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Alternate G/L Posting Code 2',
TITLE='P,C',$
FIELD=ALTERNATE_G_L_POSTING_CODE_3,
ALIAS='GLALT3', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Alternate G/L Posting Code 3',
TITLE='H,D',$
FIELD=ALTERNATE_G_L_POSTING_CODE_4,
ALIAS='GLALT4', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Alternate G/L Posting Code 4',
TITLE='H,D',$
FIELD=MULTI_CURRENCY_JOURNAL_ENTRY,
ALIAS='GLALT5', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Multi Currency Journal Entry',
TITLE='H,D',$
FIELD=CASH_BASIS_ACCOUNTING_POST_CODE,
ALIAS='GLALT6', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Cash Basis Accounting Post Code',
TITLE='H,D',$
FIELD=COMMITMENT_RELIEF_FLAG,
ALIAS='GLALT7', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Commitment Relief Flag',
TITLE='Commitment, Relief',$
FIELD=BILLING_CONTROL,
ALIAS='GLALT8', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Billing Control',
TITLE='Billing,Control',$
FIELD=CURRENCY_UPDATE,
ALIAS='GLALT9', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Currency Update',
TITLE='C,U',$
FIELD=LABOR_COSTING_FLAG,
ALIAS='GLALT0', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Labor Costing Flag',
TITLE='L,C',$
FIELD=FUTURE_USE_GLALTT,
ALIAS='GLALTT', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Future Use',
TITLE='F,U',$
FIELD=FUTURE_USE_GLALTU,
ALIAS='GLALTU', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Future Use',
TITLE='F,U',$
FIELD=FUTURE_USE_GLALTV,
ALIAS='GLALTV', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Future Use',
TITLE='F,U',$
FIELD=FUTURE_USE_GLALTW,
ALIAS='GLALTW', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Future Use',
TITLE='F,U',$
FIELD=CONSUMPTION_TAX_CROSS_REFERENCE,
ALIAS='GLALTX', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Consumption Tax Cross Reference',
TITLE='C,T',$
FIELD=FUTURE_USE_GLALTZ,
ALIAS='GLALTZ', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Future Use',
TITLE='F,U',$
FIELD=DELETE_NOT_ALLOWED,
ALIAS='GLDLNA', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Delete Not Allowed',
TITLE='Delete Not, Allowed',$
$ ACCEPT='J' OR ' ',$
FIELD=FUTURE_USE_GLCFF1,
ALIAS='GLCFF1', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Future Use',
TITLE='F,U',$
FIELD=FUTURE_USE_GLCFF2,
ALIAS='GLCFF2', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Future Use',
TITLE='F,U',$
FIELD=LEASE_COST_LEDGER_POSTED_CODE,
ALIAS='GLASM', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Lease Cost Ledger Posted Code',
TITLE='L,C',$
FIELD=BILL_CODE_CODE ,
ALIAS='GLBC', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Bill Code',
TITLE='B,C',$
FIELD=INVOICE_NUMBER,
ALIAS='GLVINV', USAGE=A25, ACTUAL=A25,
DESCRIPTION='Invoice Number',
TITLE=' Invoice, Number',$
FIELD=INVOICE_DATE,
ALIAS='GLIVD', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Invoice Date',
TITLE='Invoice, Date',$
FIELD=PHASE_CODE ,
ALIAS='GLWR01', USAGE=A4, ACTUAL=A4,
DESCRIPTION='Phase',
TITLE='Pha, se',$
FIELD=P_O__NUMBER,
ALIAS='GLPO', USAGE=A8, ACTUAL=A8,
DESCRIPTION='P_O_ Number',
TITLE='Purchase, Order',$
FIELD=PURCHASE_ORDER_SUFFIX,
ALIAS='GLPSFX', USAGE=A3, ACTUAL=A3,
DESCRIPTION='Purchase Order Suffix',
TITLE='PO,Sfx',$
FIELD=ORDER_TYPE_CODE ,
ALIAS='GLDCTO', USAGE=A2, ACTUAL=A2,
DESCRIPTION='Order Type',
TITLE='Or,Ty',$
FIELD=LINE_NUMBER,
ALIAS='GLLNID', USAGE=P7.3S , ACTUAL=P4.0,
DESCRIPTION='Line Number',
TITLE=' Line,Number',$
FIELD=FISCAL_YEAR___WEEKLY,
ALIAS='GLWY', USAGE=P3S , ACTUAL=P2,
DESCRIPTION='Fiscal Year - Weekly',
TITLE='Fiscal, Year',$
FIELD=FISCAL_PERIOD___WEEKLY,
ALIAS='GLWN', USAGE=P3S , ACTUAL=P2,
DESCRIPTION='Fiscal Period - Weekly',
TITLE='Fiscal,Period',$
FIELD=CLOSED_ITEM,
ALIAS='GLFNLP', USAGE=A1, ACTUAL=A1,
DESCRIPTION='Closed Item',
TITLE='Closed, Item',$
FIELD=OPERATIONS_SEQUENCE_NUMBER,
ALIAS='GLOPSQ', USAGE=P6.2S , ACTUAL=P5.0,
DESCRIPTION='Operations Sequence Number',
TITLE='Oper,Seq#',$
FIELD=JOB_TYPE_CODE ,
ALIAS='GLJBCD', USAGE=A6, ACTUAL=A6,
DESCRIPTION='Job Type',
TITLE=' Job, Typ',$
FIELD=JOB_STEP_CODE ,
ALIAS='GLJBST', USAGE=A4, ACTUAL=A4,
DESCRIPTION='Job Step',
TITLE='Job,Step',$
FIELD=HOME_BUSINESS_UNIT,
ALIAS='GLHMCU', USAGE=A12, ACTUAL=A12,
DESCRIPTION='Home Business Unit',
TITLE=' Home,Business Unit',$
FIELD=DOI_SUB,
ALIAS='GLDOI', USAGE=P3S , ACTUAL=P2,
DESCRIPTION='DOI Sub',
TITLE='DOI,Sub',$
FIELD=OUTSIDER_LEASE_WELL_ID,
ALIAS='GLALID', USAGE=A25, ACTUAL=A25,
DESCRIPTION='Outsider Lease/Well ID',
TITLE=' Outsider Lease, Or Well ID',$
FIELD=ID_TYPE_CODE ,
ALIAS='GLALTY', USAGE=A2, ACTUAL=A2,
DESCRIPTION='ID Type',
TITLE='I.D.,Type',$
FIELD=SERVICE_TAX_DATE,
ALIAS='GLDSVJ', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Service_Tax Date',
TITLE='Service/,Tax Date',$
FIELD=TRANSACTION_ORIGINATOR,
ALIAS='GLTORG', USAGE=A10, ACTUAL=A10,
DESCRIPTION='Transaction Originator',
TITLE='Transaction,Originator',$
FIELD=REGISTRATION_NUMBER,
ALIAS='GLREG#', USAGE=P9S , ACTUAL=P8,
DESCRIPTION='Registration Number',
TITLE='Register, Number',$
FIELD=PAYMENT_ID__INTERNAL,
ALIAS='GLPYID', USAGE=P15S , ACTUAL=P8,
DESCRIPTION='Payment ID (Internal',
TITLE=' Payment, ID',$
FIELD=USER_ID,
ALIAS='GLUSER', USAGE=A10, ACTUAL=A10,
DESCRIPTION='User ID',
TITLE=' User, ID',$
FIELD=PROGRAM_ID,
ALIAS='GLPID', USAGE=A10, ACTUAL=A10,
DESCRIPTION='Program ID',
TITLE=' Program, ID',$
FIELD=WORK_STATION_ID,
ALIAS='GLJOBN', USAGE=A10, ACTUAL=A10,
DESCRIPTION='Work Station ID',
TITLE=' Work, Stn ID',$
FIELD=DATE_UPDATED,
ALIAS='GLUPMJ', USAGE=MDYY, ACTUAL=P6JUL,
DESCRIPTION='Date Updated',
TITLE=' Date,Updated',$
FIELD=TIME_LAST_UPDATED,
ALIAS='GLUPMT', USAGE=P7 , ACTUAL=P6,
DESCRIPTION='Time Last Updated',
TITLE=' Time,Updated',$
DEFINE DOCUMENT_TYPE_DESC/A61
=JDEX(2,'00 DT',GLDCT,2,61,'A61');$
DEFINE BATCH_TYPE_DESC/A32
=JDEX(2,'98 IT',GLICUT,2,32,'A32');$
DEFINE SUBLEDGER_TYPE_DESC/A61
=JDEX(2,'00 ST',GLSBLT,1,61,'A61');$
DEFINE LEDGER_TYPE_DESC/A61
=JDEX(2,'09 LT',GLLT,2,61,'A61');$
DEFINE UNIT_OF_MEASURE_DESC/A45
=JDEX(2,'00 UM',GLUM,2,45,'A45');$
DEFINE ORIGINAL_DOCUMENT_TYPE_DESC/A61
=JDEX(2,'00 DT',GLODCT,2,61,'A61');$
DEFINE PURCHASE_ORDER_DOCUMENT_TYPE_DESC/A61
=JDEX(2,'00 DT',GLPDCT,2,61,'A61');$
DEFINE RECONCILED_DESC/A60
=JDEX(2,'09 RC',GLRCND,1,60,'A60');$
DEFINE BILL_CODE_DESC/A30
=JDEX(2,'98 BC',GLBC,1,30,'A30');$
DEFINE PHASE_DESC/A58
=JDEX(2,'00 W1',GLWR01,4,58,'A58');$
DEFINE ORDER_TYPE_DESC/A61
=JDEX(2,'00 DT',GLDCTO,2,61,'A61');$
DEFINE JOB_TYPE_DESC/A32
=JDEX(2,'06 G ',GLJBCD,6,32,'A32');$
DEFINE JOB_STEP_DESC/A24
=JDEX(2,'06 GS',GLJBST,4,24,'A24');$
DEFINE ID_TYPE_DESC/A61
=JDEX(2,'00 AL',GLALTY,2,61,'A61');$
$ Following Field Copied from File F0010
DEFINE NAME_GLCO/A30
=JDE_CO(2,'7',GLCO,5,'CCNAME',6,30,'A30');$
$ Following Field Copied from File F0006
DEFINE DESCRIPTION_GLMCU/A30
=JDE_MCU(2,'7',GLMCU,12,'MCDL01',6,30,'A30');$
$ Following Fields Copied from File F0101JB
DEFINE MAILING_NAME_GLAN8/A40
=JDE_AN8(2,'7',GLAN8,8,'WWMLNM',6,40,'A40');$
DEFINE ALPHA_NAME_GLAN8/A40
=JDE_AN8(2,'7',GLAN8,8,'WWALPH',6,40,'A40');$
DEFINE START_EFFECTIVE_DATE_GLAN8/MDYY
=JDE_AN8(2,'7',GLAN8,8,'ALEFTB',6,0,'MDYY');$
DEFINE ADDRESS_LINE_1_GLAN8/A40
=JDE_AN8(2,'7',GLAN8,8,'ALADD1',6,40,'A40');$
DEFINE ADDRESS_LINE_2_GLAN8/A40
=JDE_AN8(2,'7',GLAN8,8,'ALADD2',6,40,'A40');$
DEFINE ADDRESS_LINE_3_GLAN8/A40
=JDE_AN8(2,'7',GLAN8,8,'ALADD3',6,40,'A40');$
DEFINE ADDRESS_LINE_4_GLAN8/A40
=JDE_AN8(2,'7',GLAN8,8,'ALADD4',6,40,'A40');$
DEFINE CITY_GLAN8/A25
=JDE_AN8(2,'7',GLAN8,8,'ALCTY1',6,25,'A25');$
DEFINE STATE_CODE_GLAN8/A3
=JDE_AN8(2,'7',GLAN8,8,'ALADDS',6,3,'A3 ');$
DEFINE POSTAL_CODE_GLAN8/A12
=JDE_AN8(2,'7',GLAN8,8,'ALADDZ',6,12,'A12');$
DEFINE COUNTRY_CODE_GLAN8/A3
=JDE_AN8(2,'7',GLAN8,8,'ALCTR',5,3,'A3 ');$
DEFINE STATE_DESC/A20
=JDEX(2,'00 S ',STATE_CODE_GLAN8,3,20,'A20');$
DEFINE COUNTRY_DESC/A50
=JDEX(2,'00 CN',COUNTRY_CODE_GLAN8,3,50,'A50');$


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Member
posted Hide Post
HI, well if you are utilizing the iseries ODBC driver then this should be easily accomplished.
Open up the ODBC datasource in question, then click on the translation tab then click the checkbox " convert binary data (CCSID 65535) to text "
Hope that helps.
 
Posts: 5 | Location: NY | Registered: September 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Converting a "nonconvertible hexidecimal column" to English from an AS400 DB

Copyright © 1996-2020 Information Builders