Virtual Tables (V-Tables, SQL Views, Fake Tables)

Virtual Tables (V-Tables, SQL Views, Fake Tables)

Virtual Tables (V-Tables, SQL Views, “Fake” Tables)

Virtual tables, also known as V-Tables, SQL Views and Fake Tables are tables defined in JDE but the underlying data source is not a concrete table in the database, but is a SQL view instead. Since SQL views are technically not officially supported by JDE and require additional IT effort to implement and maintain they should be used as sparingly as possible. Having said that, V-Tables can solve many problems that the standard JDE toolset (Business Views in particular) cannot accomplish

Advantages of using a V-Table

  • Allowing more than 5 tables to be joined and more complex SQL. JDE only allows 5 tables in a BV using simple joins, 3 if using complex joins. Joins can also be done to sub-queries and all other manner of complex SQL can be implemented.
  • Performance. SQL can be written in such a way that it is more optimized than the using JDE Tools alone. Tables can be opened using the “WITH(NOLOCK)” qualifier to allow reads w/o the overhead of creating a shared lock on the records. Dirty reads are also allowed.
  • Calculated Fields. The SQL view can contain calculated fields that can in turn be used in WHERE clauses within JDE. Great care should be taken when using calculated fields to not seriously degrade performance.
  • Underlying tables used in the view do NOT necessarily need to be JDE tables. This can be used as a means to access foreign tables not defined in JDE.
  • Problem simplification. Sometimes what would be a very complex solution to a problem using just the JDE toolset can be greatly simplified by using a V-Table.
    An example of this can be seen in FV56415A which handles the co-located plant issue inside of the SQL view using a CASE statement, thus eliminating the need for complex code at the application layer inside of JDE and allowing for seamless QBE, data selection, etc. from within JDE. For more details see Inventory Co-Located Plant Tech Doc.

Disadvantages of using a V-Table

  • Increased IT effort to implement and maintain the SQL views.
  • Records returned by the views in most cases CANNOT be updated.
  • Implementation requires development and maintenance outside the JDE Toolset and OMW.
  • Portability. The SQL inside the view could potentially be database vendor specific and as such would not be portable to another database vendor.
  • Performance. V-Tables can be used to increase performance, but if used improperly can cause overall system degradation. Be very cognitive of the SQL you place inside of the view.

Note: Technically, the underlying recordset for a V-Table doesn’t have to be a SQL view, it could in fact be any database object that returns a record set such as a stored procedure. However, most of the time it will a SQL view. Stored procedures should be used with caution and as a last resort. We do not want to end up with a large portion of our “Business Logic” as stored procedures.

Steps to Create a V-Table

  • Create the SQL View.
  • Create the view in the same data source used by the target environment. Example: DV=JDE_DEVELOPMENT
  • All fields must be aliased to match the JDE V-Table prefix and column alias. Example: select f0101.aban8 as VTAN8… (Table Prefix=VT, Column Alias=AN8)
  • Database owner must = xxDTA. Example: DVDTA
  • For performance use the WITH(NOLOCK) qualifier as much as possible
  • Set the appropriate permissions on the SQL View. These permissions should match any other standard JDE Table
  • Create the JDE Table in JDE
  • Table Name should start with “FV”
  • Table Description should include the phrase “V-Table (Do Not Gen)”
  • Table prefix MUST match what was used in the SQL View. Example: VT
  • The structure of the table must match identically the structure of the result set of the SQL View
  • Do NOT generate the table or indexes in any environment.
  • Note: In some cases to get JDE to “recognize” the view, I have had to temporarily rename the SQL view, generate the table from with-in JDE, drop the table and name the SQL View back to the JDE V-Table name. I don’t know why this works, but it does. I usually only have to do this one time, in one environment, and then I never have to do it again even if I change the SQL View/V-Table
  • Test the view using UTB. If you can view records, you should now be able to use the V-Table like any other JDE Table (with the exception that you cannot insert or update records).
  • Note: Any changes made to either the SQL View or the V-Table MUST be made to both objects.

Misc V-Table Notes

  • One other disadvantage of V-Tables is that the SQL in the SQL View is not under any kind of source control. I have looked at various solutions to this including the use of VSS but have not found a really good solution. Even so, when modifying any existing SQL View, you should check VSS to see if the SQL is under any kind of source control.
Top View