[ https://issues.jboss.org/browse/TEIIDDES-2466?page=com.atlassian.jira.plu... ] Barry LaFond commented on TEIIDDES-2466: ---------------------------------------- Example Preview VDB XML with virtual base layer and top view model with a simple join table. Note that the contents of each model only contain the necessary source tables (DDL) to perform the actual query. {code:xml} <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="PREVIEW_PartsViewsTop_PartsInfo" version="1"> <description>Importer VDB</description> <property name="UseConnectorMetadata" value="true" /> <property name="deployment-name" value="PREVIEW_PartsViewsTop_PartsInfo-vdb.xml" /> <model name="PartsViewsTop" type="VIRTUAL" visible="true"> <metadata type="DDL"><![CDATA[ CREATE VIEW PartsInfo ( PART_ID string(50) NOT NULL OPTIONS(UPDATABLE 'FALSE'), PART_NAME string(255) OPTIONS(UPDATABLE 'FALSE'), PART_COLOR string(30) OPTIONS(UPDATABLE 'FALSE'), PART_WEIGHT string(255) OPTIONS(UPDATABLE 'FALSE'), SUPPLIER_ID string(10) NOT NULL OPTIONS(UPDATABLE 'FALSE') ) AS SELECT PartsViewsBase.PARTS.PART_ID, PartsViewsBase.PARTS.PART_NAME, PartsViewsBase.PARTS.PART_COLOR, PartsViewsBase.PARTS.PART_WEIGHT, PartsViewsBase.SUPPLIER_PARTS.SUPPLIER_ID FROM PartsViewsBase.PARTS, PartsViewsBase.SUPPLIER_PARTS WHERE PartsViewsBase.SUPPLIER_PARTS.PART_ID = PartsViewsBase.PARTS.PART_ID; ]]></metadata> </model> <model name="PartsViewsBase" type="VIRTUAL" visible="true"> <metadata type="DDL"><![CDATA[ CREATE VIEW SUPPLIER_PARTS ( SUPPLIER_ID string(10) NOT NULL OPTIONS(NAMEINSOURCE '"SUPPLIER_ID"', NATIVE_TYPE 'varchar', UPDATABLE 'FALSE'), PART_ID string(50) NOT NULL OPTIONS(NAMEINSOURCE '"PART_ID"', NATIVE_TYPE 'varchar', UPDATABLE 'FALSE'), QUANTITY bigdecimal OPTIONS(NAMEINSOURCE '"QUANTITY"', NATIVE_TYPE 'numeric', UPDATABLE 'FALSE', FIXED_LENGTH 'TRUE'), SHIPPER_ID bigdecimal OPTIONS(NAMEINSOURCE '"SHIPPER_ID"', NATIVE_TYPE 'numeric', UPDATABLE 'FALSE', FIXED_LENGTH 'TRUE') ) OPTIONS(NAMEINSOURCE '"dbo"."SUPPLIER_PARTS"', UPDATABLE 'FALSE') AS SELECT * FROM PartsSQL2000.SUPPLIER_PARTS; CREATE VIEW PARTS ( PART_ID string(50) NOT NULL OPTIONS(NAMEINSOURCE '"PART_ID"', NATIVE_TYPE 'varchar', UPDATABLE 'FALSE'), PART_NAME string(255) OPTIONS(NAMEINSOURCE '"PART_NAME"', NATIVE_TYPE 'varchar', UPDATABLE 'FALSE'), PART_COLOR string(30) OPTIONS(NAMEINSOURCE '"PART_COLOR"', NATIVE_TYPE 'varchar', UPDATABLE 'FALSE'), PART_WEIGHT string(255) OPTIONS(NAMEINSOURCE '"PART_WEIGHT"', NATIVE_TYPE 'varchar', UPDATABLE 'FALSE') ) OPTIONS(NAMEINSOURCE '"dbo"."PARTS"', UPDATABLE 'FALSE') AS SELECT * FROM PartsSQL2000.PARTS; ]]></metadata> </model> <model name="PartsSQL2000" type="PHYSICAL" visible="true"> <source name="PartsSQL2000" translator-name="sqlserver" connection-jndi-name="PartsSQL2000_DS" /> <metadata type="DDL"><![CDATA[ CREATE FOREIGN TABLE SUPPLIER_PARTS ( SUPPLIER_ID string(10) NOT NULL OPTIONS(NAMEINSOURCE '"SUPPLIER_ID"', NATIVE_TYPE 'varchar'), PART_ID string(50) NOT NULL OPTIONS(NAMEINSOURCE '"PART_ID"', NATIVE_TYPE 'varchar'), QUANTITY bigdecimal OPTIONS(NAMEINSOURCE '"QUANTITY"', NATIVE_TYPE 'numeric', FIXED_LENGTH 'TRUE'), SHIPPER_ID bigdecimal OPTIONS(NAMEINSOURCE '"SHIPPER_ID"', NATIVE_TYPE 'numeric', FIXED_LENGTH 'TRUE') ) OPTIONS(NAMEINSOURCE '"dbo"."SUPPLIER_PARTS"') CREATE FOREIGN TABLE PARTS ( PART_ID string(50) NOT NULL OPTIONS(NAMEINSOURCE '"PART_ID"', NATIVE_TYPE 'varchar'), PART_NAME string(255) OPTIONS(NAMEINSOURCE '"PART_NAME"', NATIVE_TYPE 'varchar'), PART_COLOR string(30) OPTIONS(NAMEINSOURCE '"PART_COLOR"', NATIVE_TYPE 'varchar'), PART_WEIGHT string(255) OPTIONS(NAMEINSOURCE '"PART_WEIGHT"', NATIVE_TYPE 'varchar') ) OPTIONS(NAMEINSOURCE '"dbo"."PARTS"') ]]></metadata> </model> </vdb> {code} -- This message was sent by Atlassian JIRA (v6.4.11#64026)