Processing External tables

jimmymac

Reputable Poster
Am looking for some ideas for the following problem. I would like to write a UBE that would be able to fetch and update data in a table outside of JDE. I also then need to write records to the F0911Z1. Of course when fetching data in a UBE you are presented with choosing the BSVW or TBLE to do Table I/O on.

There does not seem to be any way to access a non-jde table from within ER. I suppose I could write a C business function that would pass parms back and forth and perform the update/read using C. But I would prefer to do it all withing E/R.

Any suggestions would be appreciated.

Thanks.
 
jimmymac,

There are a number of ways to do what you require that have been previously discussed on the list. It depends on where the foreign table is located and its structure. You could define the table inside JDE (datasource, if needed, and table structure) provided it conforms to JDE conventions for tables. You could also use a virtual JDE table to point to a database view on the foreign table.
 
Peter,

I understand the first part of your answer where in you create a clone of the non-jde into a jde table and then use it on the table i/o (which is what we are thinking to do in our data conversion too as we have more than one table we have to extract data from to populate one z-table and table i/o doesnot allow us to access foreign tables).

But am curious to know how we can do the below :

"You could also use a virtual JDE table to point to a database view on the foreign table."

can you pls help me understand this better on how to do this?

thanks
sheeba
 
See attached
 

Attachments

  • 126577-OneWorld Database Views.doc
    36.5 KB · Views: 317
Sheeba,

Adrian has very kindly provided information to create virtual tables in the attachment to his post (thanks Adrian).

It may not be possible to create a clone of the foriegn table as the column names may not conform to JDE column naming convention (2 letter prefix for all columns in the table followed by the data dictionary alias of an appropriate data dictionary item). This is where a "virtual table" would be of great advantage. More information is available on virtual tables in JDEList. Try searching for "virtual table" or "fake table".
 
thanks Adrian. I will work on the doc and see if it solves what i am trying to achieve.

Peter - yes, i was afraid of the same (cloning problem - was actually thinking of picking just the fields i am looking for from the foreign tables and create corresponding JDE tables)- however , virtual sounds better!.

Will let you all know how it works for me.

thanks all - jdelist is THE best!

regards
sheeba
 
You're very welcome,
Please let me know if you made it (or not); I would like to know if 8.12 lets you do it.
Thank you,
 
for this you shold update injde table than this table canbe written in text or csv mode from their you can take in other datbase if you got any other idea pls send to me also thanks
 
Adrian and all,

It worked like a charm!!!

I used the documentation you attached previously and Solution Id: 200977497 (pls see my attachment for this document).

Here is what i did for my conversion.
1. Used one input table and ofcourse output to one Z file.
2. For all the other input tables where i needed to get data from - i created a database view in SQL with just the fields i wanted from that table.
3. Created corresponding tables in E1 with same field names (egIMAAD - IM column prefix plus AAD alias)
4. Created bsvw to use in table io's with Conversion.
5. Had CNC do his bit (pls see attachment for details)
6. Did a UTB on the file and made sure it pulls up data from external table - believe me - THAT was such a relief! phew!.
7. Now i use the bsvws i created in the table ios and get the data i want - yippeee....

Please contact me directly if you have questions regarding this - i will be glad to help - [email protected].

thanks to all for the help in achieving this. its a big thing for our conversion as it comes from all these different files on sql into one jde Z FILE.

thanks
sheeba
 

Attachments

  • 126949-DataBaseViewInE1.doc
    26 KB · Views: 301
Back
Top