Foreign Table within JDE

ecosentino

Member
Hello everyone,

I am trying to access a foreign table from within the JDE environment. I have read all the posts on this topic and believe I have followed all the appropriate steps. The problem is as follows
The UBE I created works as expected on the Fat Client and returns all rows I am expecting, but when I run the same UBE on the web client I get "No data selected".

We are on 9.1 on the AS400.

Here are the steps I followed
1. Created a VIEW using Create View command.
2. Created a table in E1 that matches the view I created in step 1 above. Same table name and same columns (type and size)
2.1- I did not generate this table
3. Created a E1 view based on the table from step 2.

At this point I am able to query the E1 table within the UTB on the fat client. So I assumed I was on the right track

4. Created a simple UBE, using the Business View I created, that lists all the records in the E1 table. When I run this UBE on the fat client t works as expected.
5. Deployed table, Business view and UBE to DV and PY
5. when I run the UBE on the web client, The report says no data selected.
Note: If I use databrowser from web client and query the E1 table, the records are displayed. I get the same results when I query the business view in databrowser. it is just the UBE that is not working.

Did I miss something? All suggestions would be appreciated.

Thanks,
 

Kim Schmidt

Active Member
When you say "foreign table", do you mean it's a non-E1 table on your AS400? Your first step was to create a view, so I assume you're doing this in the database.
The way I've done this on both the AS400 and in SQL Server to create such a virtual table is this:
1. Make a SQL SELECT statement that names all of the columns and returns the data you're expecting. Make sure the column names and data types match E1 data dictionary definitions.
2. Create the table and a business view in E1 that includes all of the columns. My naming convention is F5nVnnnn and V5nVnnnn, using the V to signify it's a virtual object.
3. GENERATE the table in E1.
4. In the database, execute a "DROP TABLE zzzDTA.F5nVnnnn" statement (where zzz is TEST, CRP, or PROD), otherwise the following step will fail.
5. Still in the database, convert your SELECT statement into a "CREATE VIEW zzzDTA.F5nVnnnn ..." statement and execute it.
6. Make sure your E1 security allows the intended use of the new virtual table.
I most recently did this on the AS400 to create a virtual table having a recursive left outer join of an E1 table with data selection impossible to do in E1.

Good luck!
 

DSauve

Legendary Poster
Is your UBE running on a separate batch server? If so, do you have OCM mappings defined AND active for the batch server -- if applicable -- for the table? Also, do you have the Data Source defined for the batch server -- again, if applicable?
 

johndanter

Legendary Poster
I've done this quite often and you do need to generate the table, then drop it in SQL

It maybe the grants you've given your new view and the user you use to connect to it??

Have CNC deployed this table in a package to your DV servers yet? Otherwise it won't work on the thin no
Do that then drop and recreate the view (table) again in SQL
 
Last edited:

ecosentino

Member
I am not questioning your suggestion, but am curious. If the view wasn't visible do to security, wouldn't querying the view via databrowser not work also?
 

ecosentino

Member
I did generate the table and the dropped in SQL
I have deployed the table to DV and PY. I have generated the table after it was deployed then dropped it.
 

Larry_Jones

Legendary Poster
OK, some questions and ideas for you to try:

First - what database schema is your database view in? Is it PRODDTA or something foreign to JDE? What about the underlying table?
If in fact the foreign view and/or table are in a different database / schema then that adds some variables. I know how to set it up for Oracle or SQL Server but not AS/400.

If in a different database we setup a new data source that points to that database with the appropriate settings made for Unicode, Decimal Shift, and Julian Dates. Then for each user map the database account and password to use to access the data in that data source (User Security Application (P98OWSEC Form E: Access from Form Exit in P0092) allows you to setup that information).

Another thing, and why it may work from your fat client but not the web client, is that your Fat Client probably has the ODBC Entry to access your AS/400 data source but your server's don't. So check that also.

Don't give up - you're almost there!
 

johndanter

Legendary Poster
If you have done all the steps and it works locally and it's deployed, then it should work.

If it still isn't then I did have this once and it was the way E1 formats the DD fields different to the external table. Nulls, blanks etc and the field downstream being NULL not blank

Could it be your select is using NULLS in certain fields?
Or that it's using E1 DD defaults and not translating correctly in the downstream SQL?
 
Last edited:

ecosentino

Member
so far everything has been created in our PY environment.
The foreign table is in the following library py910UCM.FUCM101P
i created the view in our E1 PY environment py910dta.F55FUCM
my create view statement is as follows
CREATE VIEW py910dta.F55FUCM as
select * from
(select userid as FCubhv1, eventtype as fcsurtyp,entrydate as fcfdt,entrytime as FCTIME,custom2 as fcpmconv1,custom3 as fcpmconv2 from py910UCM.FUCM101P where userid='ItemQuan' order by entrydate desc fetch first 100 row only) as F55FUCM

the databrowser can search F55FUCM and E1 View V55FUCM and it returns results. If the web servers didn't have ODBC entry then I would suspect that the Databrowser would not work also.
 

eydeak

Reputable Poster
I don't have any specific suggestions for your issue, but have you tried running your UBE on the server with debug logging on yet? That often helps me track down weird issues. You might try running it in debug on both the server and fat client and comparing the SQL statements to see if anything weird is happening.
 

Larry_Jones

Legendary Poster
I don't see any data typing / casting to the standard JDE data types I in your Create View Statement.

so far everything has been created in our PY environment.
The foreign table is in the following library py910UCM.FUCM101P
i created the view in our E1 PY environment py910dta.F55FUCM
my create view statement is as follows
CREATE VIEW py910dta.F55FUCM as
select * from
(select userid as FCubhv1, eventtype as fcsurtyp,entrydate as fcfdt,entrytime as FCTIME,custom2 as fcpmconv1,custom3 as fcpmconv2 from py910UCM.FUCM101P where userid='ItemQuan' order by entrydate desc fetch first 100 row only) as F55FUCM

the databrowser can search F55FUCM and E1 View V55FUCM and it returns results. If the web servers didn't have ODBC entry then I would suspect that the Databrowser would not work also.
 
Top