Getting around the 3/5 table Business View Limit

In Xe as we all know there is a 3 table limit when complex joins are involved and a 5 table limit with simple joins. I have been doing some testing and the following appears to work to get around this limitation, I was just wondering if anyone else has done this or knows of any reason why this wouldn't work (The following would be used strictly for read only applications):

1. Create a JDE table with all of the fields you want in the result set, i.e. all of the Aliases from ALL of the tables you want to join together.

2. Do NOT generate the table

3. Create a SQL view on the SQL server that has the exact same structure as the table you created in step 1, making sure to alias the field names to match the table prefix in step 1 and making sure that all the fields are in the same order as in step 1. Here you can join as many tables as you dare, as long as the result set matches the table you created in step 1.

4. Use standard JDE table I/O and tools to read from the "fake" table. JDE thinks its a table, but its really just a SQL view.


This appears to work just fine, both in UTB and using all of the C table api. I can't think of any reason why this wouldn't work... this may be a common trick, just curious if anyone else has done this or can think of any reason why this wont work. Granted, great care must be taken... but.... appears to work.

Not to burst your bubble, but if you search on "database view" or "virtual table" you will find this very topic as early as 2001. I even talked about a situation in 1999 where I needed different records of the same G/L table joined together (basically line no. = line no. + 1) with other criteria, where we used a database view to populate a fake table. This table then helped populate a financial report. I've also used it to link an access database to JDE custom tables for processing sales orders automagically.

Ben again,

LOL. Not bursting my bubble at all, in fact I was hoping to see a dozen posts saying "we've been doing that for years". It seemed like such a practical solution I figured I was far from the first to think of it. Actually after posting I talked to one of new contract CNC personel and he said he had seen that before as well. Thanks for the response. I will do some searches using the key words to see if I can learn more.

Done this many of times and it works great. This is a way to take advantage of the "the left hand doing something illegan while the right hand thinks it is okay"

There's nothing illegal about views.

The tda app just tells E1 what columns to build it's sql with and give the IDE something to show the user.

I've done similar things with integration to other systems. E1 gets to see into someone elses tables and get the required columns. It sure doesn't care as long as the odbc returns fields with the right name and right size.

Views aren't free though, you still pay the Performance Piper for those access paths.