BOster
Legendary Poster
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.
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.