1 2 Previous Next 17 Replies Latest reply on Mar 7, 2017 12:28 PM by ichanjasper

Oracle: wrong query generated for oracle 12c

ichanjasper Mar 1, 2017 2:27 PM

Hi,

I recently upgraded from Teiid 8.12.3 to version 9.1.1. And I found query transformation problem for oracle 12c.

I have a simple query. See below:

select "STORE_CITY"

from "jasperserver_FOODMART"."STORE"

limit 200001

However, teiid version 9.1.1 transformed the query to

Caused by: org.teiid.jdbc.TeiidSQLException: TEIID30504 1017993016: 1741 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT g_0."STORE_CITY" AS c_0 FROM "FOODMART".""."STORE" g_0) WHERE ROWNUM <= 200001]

at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:723)

at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:65)

at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:561)

at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:148)

at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:557)

at org.teiid.jdbc.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:317)

at org.teiid.jdbc.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:76)

It used to work in Teiid 8.12.3. Did I do something wrong?

1. Re: Oracle: wrong query generated for oracle 12c

shawkins Mar 1, 2017 2:34 PM (in response to ichanjasper)

How is the STORE table defined in the VDB? The name in source looks odd - "FOODMART".""."STORE"
Actions
2. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 1, 2017 2:37 PM (in response to shawkins)

Here is the DDL for VDB:

CREATE FOREIGN TABLE ACCOUNT (
        ACCOUNT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"ACCOUNT_ID"', NATIVE_TYPE 'NUMBER'),
        ACCOUNT_PARENT bigdecimal(22) OPTIONS (NAMEINSOURCE '"ACCOUNT_PARENT"', NATIVE_TYPE 'NUMBER'),
        ACCOUNT_DESCRIPTION string(30) OPTIONS (NAMEINSOURCE '"ACCOUNT_DESCRIPTION"', NATIVE_TYPE 'VARCHAR2'),
        ACCOUNT_TYPE string(30) NOT NULL OPTIONS (NAMEINSOURCE '"ACCOUNT_TYPE"', NATIVE_TYPE 'VARCHAR2'),
        ACCOUNT_ROLLUP string(30) NOT NULL OPTIONS (NAMEINSOURCE '"ACCOUNT_ROLLUP"', NATIVE_TYPE 'VARCHAR2'),
        CUSTOM_MEMBERS string(255) OPTIONS (NAMEINSOURCE '"CUSTOM_MEMBERS"', NATIVE_TYPE 'VARCHAR2'),
        CONSTRAINT I_ACCOUNT_ID UNIQUE(ACCOUNT_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."ACCOUNT"', UPDATABLE TRUE, CARDINALITY 11);

CREATE FOREIGN TABLE AGG_C_10_SALES_FACT_1997 (
        MONTH_OF_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"MONTH_OF_YEAR"', NATIVE_TYPE 'NUMBER'),
        QUARTER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"QUARTER"', NATIVE_TYPE 'VARCHAR2'),
        THE_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"THE_YEAR"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_COUNT"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_C_10_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 12);

CREATE FOREIGN TABLE AGG_C_14_SALES_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_ID"', NATIVE_TYPE 'NUMBER'),
        MONTH_OF_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"MONTH_OF_YEAR"', NATIVE_TYPE 'NUMBER'),
        QUARTER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"QUARTER"', NATIVE_TYPE 'VARCHAR2'),
        THE_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"THE_YEAR"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_C_14_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 86805);

CREATE FOREIGN TABLE AGG_C_SPECIAL_SALES_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_MONTH bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_MONTH"', NATIVE_TYPE 'NUMBER'),
        TIME_QUARTER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_QUARTER"', NATIVE_TYPE 'VARCHAR2'),
        TIME_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_YEAR"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES_SUM bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES_SUM"', NATIVE_TYPE 'NUMBER'),
        STORE_COST_SUM bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST_SUM"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES_SUM bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES_SUM"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_C_SPECIAL_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 86805);

CREATE FOREIGN TABLE AGG_G_MS_PCAT_SALES_FACT_1997 (
        GENDER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"GENDER"', NATIVE_TYPE 'VARCHAR2'),
        MARITAL_STATUS string(30) NOT NULL OPTIONS (NAMEINSOURCE '"MARITAL_STATUS"', NATIVE_TYPE 'VARCHAR2'),
        PRODUCT_FAMILY string(30) OPTIONS (NAMEINSOURCE '"PRODUCT_FAMILY"', NATIVE_TYPE 'VARCHAR2'),
        PRODUCT_DEPARTMENT string(30) OPTIONS (NAMEINSOURCE '"PRODUCT_DEPARTMENT"', NATIVE_TYPE 'VARCHAR2'),
        PRODUCT_CATEGORY string(30) OPTIONS (NAMEINSOURCE '"PRODUCT_CATEGORY"', NATIVE_TYPE 'VARCHAR2'),
        MONTH_OF_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"MONTH_OF_YEAR"', NATIVE_TYPE 'NUMBER'),
        QUARTER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"QUARTER"', NATIVE_TYPE 'VARCHAR2'),
        THE_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"THE_YEAR"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_COUNT"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_G_MS_PCAT_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 2637);

CREATE FOREIGN TABLE AGG_L_03_SALES_FACT_1997 (
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_L_03_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 20522);

CREATE FOREIGN TABLE AGG_L_04_SALES_FACT_1997 (
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_COUNT"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_L_04_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 323);

CREATE FOREIGN TABLE AGG_L_05_SALES_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_L_05_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 86154);

CREATE FOREIGN TABLE AGG_LC_06_SALES_FACT_1997 (
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CITY string(30) NOT NULL OPTIONS (NAMEINSOURCE '"CITY"', NATIVE_TYPE 'VARCHAR2'),
        STATE_PROVINCE string(30) NOT NULL OPTIONS (NAMEINSOURCE '"STATE_PROVINCE"', NATIVE_TYPE 'VARCHAR2'),
        COUNTRY string(30) NOT NULL OPTIONS (NAMEINSOURCE '"COUNTRY"', NATIVE_TYPE 'VARCHAR2'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_LC_06_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 0);

CREATE FOREIGN TABLE AGG_LC_100_SALES_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        QUARTER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"QUARTER"', NATIVE_TYPE 'VARCHAR2'),
        THE_YEAR bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"THE_YEAR"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_LC_100_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 86602);

CREATE FOREIGN TABLE AGG_LL_01_SALES_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_LL_01_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 86829);

CREATE FOREIGN TABLE AGG_PL_01_SALES_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES_SUM bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES_SUM"', NATIVE_TYPE 'NUMBER'),
        STORE_COST_SUM bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST_SUM"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES_SUM bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES_SUM"', NATIVE_TYPE 'NUMBER'),
        FACT_COUNT bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"FACT_COUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."AGG_PL_01_SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 86829);

CREATE FOREIGN TABLE CATEGORY (
        CATEGORY_ID string(30) NOT NULL OPTIONS (NAMEINSOURCE '"CATEGORY_ID"', NATIVE_TYPE 'VARCHAR2'),
        CATEGORY_PARENT string(30) OPTIONS (NAMEINSOURCE '"CATEGORY_PARENT"', NATIVE_TYPE 'VARCHAR2'),
        CATEGORY_DESCRIPTION string(30) NOT NULL OPTIONS (NAMEINSOURCE '"CATEGORY_DESCRIPTION"', NATIVE_TYPE 'VARCHAR2'),
        CATEGORY_ROLLUP string(30) OPTIONS (NAMEINSOURCE '"CATEGORY_ROLLUP"', NATIVE_TYPE 'VARCHAR2'),
        CONSTRAINT I_CATEGORY_ID UNIQUE(CATEGORY_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."CATEGORY"', UPDATABLE TRUE, CARDINALITY 4);

CREATE FOREIGN TABLE CURRENCY (
        CURRENCY_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CURRENCY_ID"', NATIVE_TYPE 'NUMBER'),
        "DATE" timestamp NOT NULL OPTIONS (NAMEINSOURCE '"DATE"', NATIVE_TYPE 'DATE'),
        CURRENCY string(30) NOT NULL OPTIONS (NAMEINSOURCE '"CURRENCY"', NATIVE_TYPE 'VARCHAR2'),
        CONVERSION_RATIO bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"CONVERSION_RATIO"', NATIVE_TYPE 'NUMBER'),
        CONSTRAINT I_CURRENCY UNIQUE(CURRENCY_ID, "DATE")
) OPTIONS (NAMEINSOURCE '"FOODMART".""."CURRENCY"', UPDATABLE TRUE, CARDINALITY 72);

CREATE FOREIGN TABLE CUSTOMER (
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        ACCOUNT_NUM bigdecimal(15) NOT NULL OPTIONS (NAMEINSOURCE '"ACCOUNT_NUM"', NATIVE_TYPE 'NUMBER'),
        LNAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"LNAME"', NATIVE_TYPE 'VARCHAR2'),
        FNAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"FNAME"', NATIVE_TYPE 'VARCHAR2'),
        MI string(30) OPTIONS (NAMEINSOURCE '"MI"', NATIVE_TYPE 'VARCHAR2'),
        ADDRESS1 string(30) OPTIONS (NAMEINSOURCE '"ADDRESS1"', NATIVE_TYPE 'VARCHAR2'),
        ADDRESS2 string(30) OPTIONS (NAMEINSOURCE '"ADDRESS2"', NATIVE_TYPE 'VARCHAR2'),
        ADDRESS3 string(30) OPTIONS (NAMEINSOURCE '"ADDRESS3"', NATIVE_TYPE 'VARCHAR2'),
        ADDRESS4 string(30) OPTIONS (NAMEINSOURCE '"ADDRESS4"', NATIVE_TYPE 'VARCHAR2'),
        CITY string(30) OPTIONS (NAMEINSOURCE '"CITY"', NATIVE_TYPE 'VARCHAR2'),
        STATE_PROVINCE string(30) OPTIONS (NAMEINSOURCE '"STATE_PROVINCE"', NATIVE_TYPE 'VARCHAR2'),
        POSTAL_CODE string(30) NOT NULL OPTIONS (NAMEINSOURCE '"POSTAL_CODE"', NATIVE_TYPE 'VARCHAR2'),
        COUNTRY string(30) NOT NULL OPTIONS (NAMEINSOURCE '"COUNTRY"', NATIVE_TYPE 'VARCHAR2'),
        CUSTOMER_REGION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_REGION_ID"', NATIVE_TYPE 'NUMBER'),
        PHONE1 string(30) NOT NULL OPTIONS (NAMEINSOURCE '"PHONE1"', NATIVE_TYPE 'VARCHAR2'),
        PHONE2 string(30) NOT NULL OPTIONS (NAMEINSOURCE '"PHONE2"', NATIVE_TYPE 'VARCHAR2'),
        BIRTHDATE timestamp NOT NULL OPTIONS (NAMEINSOURCE '"BIRTHDATE"', NATIVE_TYPE 'DATE'),
        MARITAL_STATUS string(30) NOT NULL OPTIONS (NAMEINSOURCE '"MARITAL_STATUS"', NATIVE_TYPE 'VARCHAR2'),
        YEARLY_INCOME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"YEARLY_INCOME"', NATIVE_TYPE 'VARCHAR2'),
        GENDER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"GENDER"', NATIVE_TYPE 'VARCHAR2'),
        TOTAL_CHILDREN bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"TOTAL_CHILDREN"', NATIVE_TYPE 'NUMBER'),
        NUM_CHILDREN_AT_HOME bigdecimal(10) NOT NULL OPTIONS (NAMEINSOURCE '"NUM_CHILDREN_AT_HOME"', NATIVE_TYPE 'NUMBER'),
        EDUCATION string(30) NOT NULL OPTIONS (NAMEINSOURCE '"EDUCATION"', NATIVE_TYPE 'VARCHAR2'),
        DATE_ACCNT_OPENED timestamp NOT NULL OPTIONS (NAMEINSOURCE '"DATE_ACCNT_OPENED"', NATIVE_TYPE 'DATE'),
        MEMBER_CARD string(30) OPTIONS (NAMEINSOURCE '"MEMBER_CARD"', NATIVE_TYPE 'VARCHAR2'),
        OCCUPATION string(30) OPTIONS (NAMEINSOURCE '"OCCUPATION"', NATIVE_TYPE 'VARCHAR2'),
        HOUSEOWNER string(30) OPTIONS (NAMEINSOURCE '"HOUSEOWNER"', NATIVE_TYPE 'VARCHAR2'),
        NUM_CARS_OWNED bigdecimal(22) OPTIONS (NAMEINSOURCE '"NUM_CARS_OWNED"', NATIVE_TYPE 'NUMBER'),
        FULLNAME string(60) NOT NULL OPTIONS (NAMEINSOURCE '"FULLNAME"', NATIVE_TYPE 'VARCHAR2'),
        CONSTRAINT I_CUSTOMER_ID UNIQUE(CUSTOMER_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."CUSTOMER"', UPDATABLE TRUE, CARDINALITY 10281);

CREATE FOREIGN TABLE CUSTOMER_SALES (
        CUSTOMER_FULLNAME string(60) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_FULLNAME"', NATIVE_TYPE 'VARCHAR2'),
        STORE_SALES bigdecimal OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."CUSTOMER_SALES"', UPDATABLE TRUE, CARDINALITY 5581);

CREATE FOREIGN TABLE DAYS (
        "DAY" bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"DAY"', NATIVE_TYPE 'NUMBER'),
        WEEK_DAY string(30) NOT NULL OPTIONS (NAMEINSOURCE '"WEEK_DAY"', NATIVE_TYPE 'VARCHAR2')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."DAYS"', UPDATABLE TRUE, CARDINALITY 7);

CREATE FOREIGN TABLE DEPARTMENT (
        DEPARTMENT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"DEPARTMENT_ID"', NATIVE_TYPE 'NUMBER'),
        DEPARTMENT_DESCRIPTION string(30) NOT NULL OPTIONS (NAMEINSOURCE '"DEPARTMENT_DESCRIPTION"', NATIVE_TYPE 'VARCHAR2'),
        CONSTRAINT I_DEPARTMENT_ID UNIQUE(DEPARTMENT_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."DEPARTMENT"', UPDATABLE TRUE, CARDINALITY 12);

CREATE FOREIGN TABLE EMPLOYEE (
        EMPLOYEE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"EMPLOYEE_ID"', NATIVE_TYPE 'NUMBER'),
        FULL_NAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"FULL_NAME"', NATIVE_TYPE 'VARCHAR2'),
        FIRST_NAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"FIRST_NAME"', NATIVE_TYPE 'VARCHAR2'),
        LAST_NAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"LAST_NAME"', NATIVE_TYPE 'VARCHAR2'),
        POSITION_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"POSITION_ID"', NATIVE_TYPE 'NUMBER'),
        POSITION_TITLE string(30) OPTIONS (NAMEINSOURCE '"POSITION_TITLE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        DEPARTMENT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"DEPARTMENT_ID"', NATIVE_TYPE 'NUMBER'),
        BIRTH_DATE timestamp NOT NULL OPTIONS (NAMEINSOURCE '"BIRTH_DATE"', NATIVE_TYPE 'DATE'),
        HIRE_DATE timestamp OPTIONS (NAMEINSOURCE '"HIRE_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        END_DATE timestamp OPTIONS (NAMEINSOURCE '"END_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        SALARY bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"SALARY"', NATIVE_TYPE 'NUMBER'),
        SUPERVISOR_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"SUPERVISOR_ID"', NATIVE_TYPE 'NUMBER'),
        EDUCATION_LEVEL string(30) NOT NULL OPTIONS (NAMEINSOURCE '"EDUCATION_LEVEL"', NATIVE_TYPE 'VARCHAR2'),
        MARITAL_STATUS string(30) NOT NULL OPTIONS (NAMEINSOURCE '"MARITAL_STATUS"', NATIVE_TYPE 'VARCHAR2'),
        GENDER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"GENDER"', NATIVE_TYPE 'VARCHAR2'),
        MANAGEMENT_ROLE string(30) OPTIONS (NAMEINSOURCE '"MANAGEMENT_ROLE"', NATIVE_TYPE 'VARCHAR2'),
        CONSTRAINT I_EMPLOYEE_ID UNIQUE(EMPLOYEE_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."EMPLOYEE"', UPDATABLE TRUE, CARDINALITY 1155);

CREATE FOREIGN TABLE EMPLOYEE_CLOSURE (
        EMPLOYEE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"EMPLOYEE_ID"', NATIVE_TYPE 'NUMBER'),
        SUPERVISOR_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"SUPERVISOR_ID"', NATIVE_TYPE 'NUMBER'),
        DISTANCE bigdecimal(22) OPTIONS (NAMEINSOURCE '"DISTANCE"', NATIVE_TYPE 'NUMBER'),
        CONSTRAINT I_EMPL_CLOSURE UNIQUE(SUPERVISOR_ID, EMPLOYEE_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."EMPLOYEE_CLOSURE"', UPDATABLE TRUE, CARDINALITY 7179);

CREATE FOREIGN TABLE EXPENSE_FACT (
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        ACCOUNT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"ACCOUNT_ID"', NATIVE_TYPE 'NUMBER'),
        EXP_DATE timestamp NOT NULL OPTIONS (NAMEINSOURCE '"EXP_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CATEGORY_ID string(30) NOT NULL OPTIONS (NAMEINSOURCE '"CATEGORY_ID"', NATIVE_TYPE 'VARCHAR2'),
        CURRENCY_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CURRENCY_ID"', NATIVE_TYPE 'NUMBER'),
        AMOUNT bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"AMOUNT"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."EXPENSE_FACT"', UPDATABLE TRUE, CARDINALITY 2400);

CREATE FOREIGN TABLE INVENTORY_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"WAREHOUSE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        UNITS_ORDERED bigdecimal(22) OPTIONS (NAMEINSOURCE '"UNITS_ORDERED"', NATIVE_TYPE 'NUMBER'),
        UNITS_SHIPPED bigdecimal(22) OPTIONS (NAMEINSOURCE '"UNITS_SHIPPED"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_SALES bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"WAREHOUSE_SALES"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_COST bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"WAREHOUSE_COST"', NATIVE_TYPE 'NUMBER'),
        SUPPLY_TIME bigdecimal(10) OPTIONS (NAMEINSOURCE '"SUPPLY_TIME"', NATIVE_TYPE 'NUMBER'),
        STORE_INVOICE bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"STORE_INVOICE"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."INVENTORY_FACT_1997"', UPDATABLE TRUE, CARDINALITY 4070);

CREATE FOREIGN TABLE INVENTORY_FACT_1998 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"WAREHOUSE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        UNITS_ORDERED bigdecimal(22) OPTIONS (NAMEINSOURCE '"UNITS_ORDERED"', NATIVE_TYPE 'NUMBER'),
        UNITS_SHIPPED bigdecimal(22) OPTIONS (NAMEINSOURCE '"UNITS_SHIPPED"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_SALES bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"WAREHOUSE_SALES"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_COST bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"WAREHOUSE_COST"', NATIVE_TYPE 'NUMBER'),
        SUPPLY_TIME bigdecimal(10) OPTIONS (NAMEINSOURCE '"SUPPLY_TIME"', NATIVE_TYPE 'NUMBER'),
        STORE_INVOICE bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"STORE_INVOICE"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."INVENTORY_FACT_1998"', UPDATABLE TRUE, CARDINALITY 7282);

CREATE FOREIGN TABLE MONTHLY_PROFIT (
        THE_DATE timestamp OPTIONS (NAMEINSOURCE '"THE_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        THE_YEAR bigdecimal(10) OPTIONS (NAMEINSOURCE '"THE_YEAR"', NATIVE_TYPE 'NUMBER'),
        THE_MONTH string(30) OPTIONS (NAMEINSOURCE '"THE_MONTH"', NATIVE_TYPE 'VARCHAR2'),
        THE_QUARTER string(30) OPTIONS (NAMEINSOURCE '"THE_QUARTER"', NATIVE_TYPE 'VARCHAR2'),
        TIME_BY_DAY_MONTH_OF_YEAR bigdecimal(10) OPTIONS (NAMEINSOURCE '"TIME_BY_DAY_MONTH_OF_YEAR"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        PROFIT bigdecimal OPTIONS (NAMEINSOURCE '"PROFIT"', NATIVE_TYPE 'NUMBER'),
        STORE_STATE string(30) OPTIONS (NAMEINSOURCE '"STORE_STATE"', NATIVE_TYPE 'VARCHAR2')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."MONTHLY_PROFIT"', UPDATABLE TRUE, CARDINALITY 36);

CREATE FOREIGN TABLE POSITION (
        POSITION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"POSITION_ID"', NATIVE_TYPE 'NUMBER'),
        POSITION_TITLE string(30) NOT NULL OPTIONS (NAMEINSOURCE '"POSITION_TITLE"', NATIVE_TYPE 'VARCHAR2'),
        PAY_TYPE string(30) NOT NULL OPTIONS (NAMEINSOURCE '"PAY_TYPE"', NATIVE_TYPE 'VARCHAR2'),
        MIN_SCALE bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"MIN_SCALE"', NATIVE_TYPE 'NUMBER'),
        MAX_SCALE bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"MAX_SCALE"', NATIVE_TYPE 'NUMBER'),
        MANAGEMENT_ROLE string(30) NOT NULL OPTIONS (NAMEINSOURCE '"MANAGEMENT_ROLE"', NATIVE_TYPE 'VARCHAR2'),
        CONSTRAINT I_POSITION_ID UNIQUE(POSITION_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."POSITION"', UPDATABLE TRUE, CARDINALITY 18);

CREATE FOREIGN TABLE PRODUCT (
        PRODUCT_CLASS_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_CLASS_ID"', NATIVE_TYPE 'NUMBER'),
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        BRAND_NAME string(60) OPTIONS (NAMEINSOURCE '"BRAND_NAME"', NATIVE_TYPE 'VARCHAR2'),
        PRODUCT_NAME string(60) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_NAME"', NATIVE_TYPE 'VARCHAR2'),
        SKU bigdecimal(15) NOT NULL OPTIONS (NAMEINSOURCE '"SKU"', NATIVE_TYPE 'NUMBER'),
        SRP bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"SRP"', NATIVE_TYPE 'NUMBER'),
        GROSS_WEIGHT double OPTIONS (NAMEINSOURCE '"GROSS_WEIGHT"', NATIVE_TYPE 'FLOAT'),
        NET_WEIGHT double OPTIONS (NAMEINSOURCE '"NET_WEIGHT"', NATIVE_TYPE 'FLOAT'),
        RECYCLABLE_PACKAGE bigdecimal(38) OPTIONS (NAMEINSOURCE '"RECYCLABLE_PACKAGE"', NATIVE_TYPE 'NUMBER'),
        LOW_FAT bigdecimal(38) OPTIONS (NAMEINSOURCE '"LOW_FAT"', NATIVE_TYPE 'NUMBER'),
        UNITS_PER_CASE bigdecimal(10) OPTIONS (NAMEINSOURCE '"UNITS_PER_CASE"', NATIVE_TYPE 'NUMBER'),
        CASES_PER_PALLET bigdecimal(10) OPTIONS (NAMEINSOURCE '"CASES_PER_PALLET"', NATIVE_TYPE 'NUMBER'),
        SHELF_WIDTH double OPTIONS (NAMEINSOURCE '"SHELF_WIDTH"', NATIVE_TYPE 'FLOAT'),
        SHELF_HEIGHT double OPTIONS (NAMEINSOURCE '"SHELF_HEIGHT"', NATIVE_TYPE 'FLOAT'),
        SHELF_DEPTH double OPTIONS (NAMEINSOURCE '"SHELF_DEPTH"', NATIVE_TYPE 'FLOAT'),
        CONSTRAINT I_PRODUCT_ID UNIQUE(PRODUCT_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."PRODUCT"', UPDATABLE TRUE, CARDINALITY 1560);

CREATE FOREIGN TABLE PRODUCT_CLASS (
        PRODUCT_CLASS_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_CLASS_ID"', NATIVE_TYPE 'NUMBER'),
        PRODUCT_SUBCATEGORY string(30) OPTIONS (NAMEINSOURCE '"PRODUCT_SUBCATEGORY"', NATIVE_TYPE 'VARCHAR2'),
        PRODUCT_CATEGORY string(30) OPTIONS (NAMEINSOURCE '"PRODUCT_CATEGORY"', NATIVE_TYPE 'VARCHAR2'),
        PRODUCT_DEPARTMENT string(30) OPTIONS (NAMEINSOURCE '"PRODUCT_DEPARTMENT"', NATIVE_TYPE 'VARCHAR2'),
        PRODUCT_FAMILY string(30) OPTIONS (NAMEINSOURCE '"PRODUCT_FAMILY"', NATIVE_TYPE 'VARCHAR2')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."PRODUCT_CLASS"', UPDATABLE TRUE, CARDINALITY 110);

CREATE FOREIGN TABLE PRODUCT_SALES (
        PRODUCT_PRODUCT_NAME string(60) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_PRODUCT_NAME"', NATIVE_TYPE 'VARCHAR2'),
        STORE_SALES bigdecimal OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        PRODUCT_PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_PRODUCT_ID"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."PRODUCT_SALES"', UPDATABLE TRUE, CARDINALITY 1559);

CREATE FOREIGN TABLE PROMOTION (
        PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_DISTRICT_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"PROMOTION_DISTRICT_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_NAME string(30) OPTIONS (NAMEINSOURCE '"PROMOTION_NAME"', NATIVE_TYPE 'VARCHAR2'),
        MEDIA_TYPE string(30) OPTIONS (NAMEINSOURCE '"MEDIA_TYPE"', NATIVE_TYPE 'VARCHAR2'),
        COST bigdecimal(10,4) OPTIONS (NAMEINSOURCE '"COST"', NATIVE_TYPE 'NUMBER'),
        START_DATE timestamp OPTIONS (NAMEINSOURCE '"START_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        END_DATE timestamp OPTIONS (NAMEINSOURCE '"END_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        CONSTRAINT I_PROMOTION_ID UNIQUE(PROMOTION_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."PROMOTION"', UPDATABLE TRUE, CARDINALITY 1864);

CREATE FOREIGN TABLE PROMOTION_SALES (
        PROMOTION_PROMOTION_NAME string(30) OPTIONS (NAMEINSOURCE '"PROMOTION_PROMOTION_NAME"', NATIVE_TYPE 'VARCHAR2'),
        STORE_SALES bigdecimal OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_PROMOTION_ID"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."PROMOTION_SALES"', UPDATABLE TRUE, CARDINALITY 148);

CREATE FOREIGN TABLE REGION (
        REGION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"REGION_ID"', NATIVE_TYPE 'NUMBER'),
        SALES_CITY string(30) OPTIONS (NAMEINSOURCE '"SALES_CITY"', NATIVE_TYPE 'VARCHAR2'),
        SALES_STATE_PROVINCE string(30) OPTIONS (NAMEINSOURCE '"SALES_STATE_PROVINCE"', NATIVE_TYPE 'VARCHAR2'),
        SALES_DISTRICT string(30) OPTIONS (NAMEINSOURCE '"SALES_DISTRICT"', NATIVE_TYPE 'VARCHAR2'),
        SALES_REGION string(30) OPTIONS (NAMEINSOURCE '"SALES_REGION"', NATIVE_TYPE 'VARCHAR2'),
        SALES_COUNTRY string(30) OPTIONS (NAMEINSOURCE '"SALES_COUNTRY"', NATIVE_TYPE 'VARCHAR2'),
        SALES_DISTRICT_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"SALES_DISTRICT_ID"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."REGION"', UPDATABLE TRUE, CARDINALITY 110);

CREATE FOREIGN TABLE RESERVE_EMPLOYEE (
        EMPLOYEE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"EMPLOYEE_ID"', NATIVE_TYPE 'NUMBER'),
        FULL_NAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"FULL_NAME"', NATIVE_TYPE 'VARCHAR2'),
        FIRST_NAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"FIRST_NAME"', NATIVE_TYPE 'VARCHAR2'),
        LAST_NAME string(30) NOT NULL OPTIONS (NAMEINSOURCE '"LAST_NAME"', NATIVE_TYPE 'VARCHAR2'),
        POSITION_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"POSITION_ID"', NATIVE_TYPE 'NUMBER'),
        POSITION_TITLE string(30) OPTIONS (NAMEINSOURCE '"POSITION_TITLE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        DEPARTMENT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"DEPARTMENT_ID"', NATIVE_TYPE 'NUMBER'),
        BIRTH_DATE timestamp NOT NULL OPTIONS (NAMEINSOURCE '"BIRTH_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        HIRE_DATE timestamp OPTIONS (NAMEINSOURCE '"HIRE_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        END_DATE timestamp OPTIONS (NAMEINSOURCE '"END_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        SALARY bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"SALARY"', NATIVE_TYPE 'NUMBER'),
        SUPERVISOR_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"SUPERVISOR_ID"', NATIVE_TYPE 'NUMBER'),
        EDUCATION_LEVEL string(30) NOT NULL OPTIONS (NAMEINSOURCE '"EDUCATION_LEVEL"', NATIVE_TYPE 'VARCHAR2'),
        MARITAL_STATUS string(30) NOT NULL OPTIONS (NAMEINSOURCE '"MARITAL_STATUS"', NATIVE_TYPE 'VARCHAR2'),
        GENDER string(30) NOT NULL OPTIONS (NAMEINSOURCE '"GENDER"', NATIVE_TYPE 'VARCHAR2'),
        CONSTRAINT I_RSRV_EMPL_ID UNIQUE(EMPLOYEE_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."RESERVE_EMPLOYEE"', UPDATABLE TRUE, CARDINALITY 143);

CREATE FOREIGN TABLE SALARY (
        PAY_DATE timestamp NOT NULL OPTIONS (NAMEINSOURCE '"PAY_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        EMPLOYEE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"EMPLOYEE_ID"', NATIVE_TYPE 'NUMBER'),
        DEPARTMENT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"DEPARTMENT_ID"', NATIVE_TYPE 'NUMBER'),
        CURRENCY_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CURRENCY_ID"', NATIVE_TYPE 'NUMBER'),
        SALARY_PAID bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"SALARY_PAID"', NATIVE_TYPE 'NUMBER'),
        OVERTIME_PAID bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"OVERTIME_PAID"', NATIVE_TYPE 'NUMBER'),
        VACATION_ACCRUED double NOT NULL OPTIONS (NAMEINSOURCE '"VACATION_ACCRUED"', NATIVE_TYPE 'FLOAT'),
        VACATION_USED double NOT NULL OPTIONS (NAMEINSOURCE '"VACATION_USED"', NATIVE_TYPE 'FLOAT')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."SALARY"', UPDATABLE TRUE, CARDINALITY 21252);

CREATE FOREIGN TABLE SALES_FACT_1997 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."SALES_FACT_1997"', UPDATABLE TRUE, CARDINALITY 86837);

CREATE FOREIGN TABLE SALES_FACT_1998 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."SALES_FACT_1998"', UPDATABLE TRUE, CARDINALITY 164558);

CREATE FOREIGN TABLE SALES_FACT_DEC_1998 (
        PRODUCT_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PRODUCT_ID"', NATIVE_TYPE 'NUMBER'),
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        CUSTOMER_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"CUSTOMER_ID"', NATIVE_TYPE 'NUMBER'),
        PROMOTION_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"PROMOTION_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_SALES"', NATIVE_TYPE 'NUMBER'),
        STORE_COST bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_COST"', NATIVE_TYPE 'NUMBER'),
        UNIT_SALES bigdecimal(10,4) NOT NULL OPTIONS (NAMEINSOURCE '"UNIT_SALES"', NATIVE_TYPE 'NUMBER')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."SALES_FACT_DEC_1998"', UPDATABLE TRUE, CARDINALITY 18325);

CREATE FOREIGN TABLE STORE (
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_TYPE string(30) OPTIONS (NAMEINSOURCE '"STORE_TYPE"', NATIVE_TYPE 'VARCHAR2'),
        REGION_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"REGION_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_NAME string(30) OPTIONS (NAMEINSOURCE '"STORE_NAME"', NATIVE_TYPE 'VARCHAR2'),
        STORE_NUMBER bigdecimal(22) OPTIONS (NAMEINSOURCE '"STORE_NUMBER"', NATIVE_TYPE 'NUMBER'),
        STORE_STREET_ADDRESS string(30) OPTIONS (NAMEINSOURCE '"STORE_STREET_ADDRESS"', NATIVE_TYPE 'VARCHAR2'),
        STORE_CITY string(30) OPTIONS (NAMEINSOURCE '"STORE_CITY"', NATIVE_TYPE 'VARCHAR2'),
        STORE_STATE string(30) OPTIONS (NAMEINSOURCE '"STORE_STATE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_POSTAL_CODE string(30) OPTIONS (NAMEINSOURCE '"STORE_POSTAL_CODE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_COUNTRY string(30) OPTIONS (NAMEINSOURCE '"STORE_COUNTRY"', NATIVE_TYPE 'VARCHAR2'),
        STORE_MANAGER string(30) OPTIONS (NAMEINSOURCE '"STORE_MANAGER"', NATIVE_TYPE 'VARCHAR2'),
        STORE_PHONE string(30) OPTIONS (NAMEINSOURCE '"STORE_PHONE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_FAX string(30) OPTIONS (NAMEINSOURCE '"STORE_FAX"', NATIVE_TYPE 'VARCHAR2'),
        FIRST_OPENED_DATE timestamp OPTIONS (NAMEINSOURCE '"FIRST_OPENED_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        LAST_REMODEL_DATE timestamp OPTIONS (NAMEINSOURCE '"LAST_REMODEL_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        STORE_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"STORE_SQFT"', NATIVE_TYPE 'NUMBER'),
        GROCERY_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"GROCERY_SQFT"', NATIVE_TYPE 'NUMBER'),
        FROZEN_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"FROZEN_SQFT"', NATIVE_TYPE 'NUMBER'),
        MEAT_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"MEAT_SQFT"', NATIVE_TYPE 'NUMBER'),
        COFFEE_BAR bigdecimal(38) OPTIONS (NAMEINSOURCE '"COFFEE_BAR"', NATIVE_TYPE 'NUMBER'),
        VIDEO_STORE bigdecimal(38) OPTIONS (NAMEINSOURCE '"VIDEO_STORE"', NATIVE_TYPE 'NUMBER'),
        SALAD_BAR bigdecimal(38) OPTIONS (NAMEINSOURCE '"SALAD_BAR"', NATIVE_TYPE 'NUMBER'),
        PREPARED_FOOD bigdecimal(38) OPTIONS (NAMEINSOURCE '"PREPARED_FOOD"', NATIVE_TYPE 'NUMBER'),
        FLORIST bigdecimal(38) OPTIONS (NAMEINSOURCE '"FLORIST"', NATIVE_TYPE 'NUMBER'),
        CONSTRAINT I_STORE_ID UNIQUE(STORE_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."STORE"', UPDATABLE TRUE, CARDINALITY 25);

CREATE FOREIGN TABLE STORE_RAGGED (
        STORE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"STORE_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_TYPE string(30) OPTIONS (NAMEINSOURCE '"STORE_TYPE"', NATIVE_TYPE 'VARCHAR2'),
        REGION_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"REGION_ID"', NATIVE_TYPE 'NUMBER'),
        STORE_NAME string(30) OPTIONS (NAMEINSOURCE '"STORE_NAME"', NATIVE_TYPE 'VARCHAR2'),
        STORE_NUMBER bigdecimal(22) OPTIONS (NAMEINSOURCE '"STORE_NUMBER"', NATIVE_TYPE 'NUMBER'),
        STORE_STREET_ADDRESS string(30) OPTIONS (NAMEINSOURCE '"STORE_STREET_ADDRESS"', NATIVE_TYPE 'VARCHAR2'),
        STORE_CITY string(30) OPTIONS (NAMEINSOURCE '"STORE_CITY"', NATIVE_TYPE 'VARCHAR2'),
        STORE_STATE string(30) OPTIONS (NAMEINSOURCE '"STORE_STATE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_POSTAL_CODE string(30) OPTIONS (NAMEINSOURCE '"STORE_POSTAL_CODE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_COUNTRY string(30) OPTIONS (NAMEINSOURCE '"STORE_COUNTRY"', NATIVE_TYPE 'VARCHAR2'),
        STORE_MANAGER string(30) OPTIONS (NAMEINSOURCE '"STORE_MANAGER"', NATIVE_TYPE 'VARCHAR2'),
        STORE_PHONE string(30) OPTIONS (NAMEINSOURCE '"STORE_PHONE"', NATIVE_TYPE 'VARCHAR2'),
        STORE_FAX string(30) OPTIONS (NAMEINSOURCE '"STORE_FAX"', NATIVE_TYPE 'VARCHAR2'),
        FIRST_OPENED_DATE timestamp OPTIONS (NAMEINSOURCE '"FIRST_OPENED_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        LAST_REMODEL_DATE timestamp OPTIONS (NAMEINSOURCE '"LAST_REMODEL_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        STORE_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"STORE_SQFT"', NATIVE_TYPE 'NUMBER'),
        GROCERY_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"GROCERY_SQFT"', NATIVE_TYPE 'NUMBER'),
        FROZEN_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"FROZEN_SQFT"', NATIVE_TYPE 'NUMBER'),
        MEAT_SQFT bigdecimal(22) OPTIONS (NAMEINSOURCE '"MEAT_SQFT"', NATIVE_TYPE 'NUMBER'),
        COFFEE_BAR bigdecimal(38) OPTIONS (NAMEINSOURCE '"COFFEE_BAR"', NATIVE_TYPE 'NUMBER'),
        VIDEO_STORE bigdecimal(38) OPTIONS (NAMEINSOURCE '"VIDEO_STORE"', NATIVE_TYPE 'NUMBER'),
        SALAD_BAR bigdecimal(38) OPTIONS (NAMEINSOURCE '"SALAD_BAR"', NATIVE_TYPE 'NUMBER'),
        PREPARED_FOOD bigdecimal(38) OPTIONS (NAMEINSOURCE '"PREPARED_FOOD"', NATIVE_TYPE 'NUMBER'),
        FLORIST bigdecimal(38) OPTIONS (NAMEINSOURCE '"FLORIST"', NATIVE_TYPE 'NUMBER'),
        CONSTRAINT I_STORE_RAGGD_ID UNIQUE(STORE_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."STORE_RAGGED"', UPDATABLE TRUE, CARDINALITY 25);

CREATE FOREIGN TABLE TIME_BY_DAY (
        TIME_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"TIME_ID"', NATIVE_TYPE 'NUMBER'),
        THE_DATE timestamp OPTIONS (NAMEINSOURCE '"THE_DATE"', NATIVE_TYPE 'TIMESTAMP'),
        THE_DAY string(30) OPTIONS (NAMEINSOURCE '"THE_DAY"', NATIVE_TYPE 'VARCHAR2'),
        THE_MONTH string(30) OPTIONS (NAMEINSOURCE '"THE_MONTH"', NATIVE_TYPE 'VARCHAR2'),
        THE_YEAR bigdecimal(10) OPTIONS (NAMEINSOURCE '"THE_YEAR"', NATIVE_TYPE 'NUMBER'),
        DAY_OF_MONTH bigdecimal(10) OPTIONS (NAMEINSOURCE '"DAY_OF_MONTH"', NATIVE_TYPE 'NUMBER'),
        WEEK_OF_YEAR bigdecimal(22) OPTIONS (NAMEINSOURCE '"WEEK_OF_YEAR"', NATIVE_TYPE 'NUMBER'),
        MONTH_OF_YEAR bigdecimal(10) OPTIONS (NAMEINSOURCE '"MONTH_OF_YEAR"', NATIVE_TYPE 'NUMBER'),
        QUARTER string(30) OPTIONS (NAMEINSOURCE '"QUARTER"', NATIVE_TYPE 'VARCHAR2'),
        FISCAL_PERIOD string(30) OPTIONS (NAMEINSOURCE '"FISCAL_PERIOD"', NATIVE_TYPE 'VARCHAR2'),
        DAY_OF_WEEK bigdecimal(38) OPTIONS (NAMEINSOURCE '"DAY_OF_WEEK"', NATIVE_TYPE 'NUMBER'),
        CONSTRAINT I_TIME_DAY UNIQUE(THE_DATE),
        CONSTRAINT I_TIME_ID UNIQUE(TIME_ID)
) OPTIONS (NAMEINSOURCE '"FOODMART".""."TIME_BY_DAY"', UPDATABLE TRUE, CARDINALITY 730);

CREATE FOREIGN TABLE WAREHOUSE (
        WAREHOUSE_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"WAREHOUSE_ID"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_CLASS_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"WAREHOUSE_CLASS_ID"', NATIVE_TYPE 'NUMBER'),
        STORES_ID bigdecimal(22) OPTIONS (NAMEINSOURCE '"STORES_ID"', NATIVE_TYPE 'NUMBER'),
        WAREHOUSE_NAME string(60) OPTIONS (NAMEINSOURCE '"WAREHOUSE_NAME"', NATIVE_TYPE 'VARCHAR2'),
        WA_ADDRESS1 string(30) OPTIONS (NAMEINSOURCE '"WA_ADDRESS1"', NATIVE_TYPE 'VARCHAR2'),
        WA_ADDRESS2 string(30) OPTIONS (NAMEINSOURCE '"WA_ADDRESS2"', NATIVE_TYPE 'VARCHAR2'),
        WA_ADDRESS3 string(30) OPTIONS (NAMEINSOURCE '"WA_ADDRESS3"', NATIVE_TYPE 'VARCHAR2'),
        WA_ADDRESS4 string(30) OPTIONS (NAMEINSOURCE '"WA_ADDRESS4"', NATIVE_TYPE 'VARCHAR2'),
        WAREHOUSE_CITY string(30) OPTIONS (NAMEINSOURCE '"WAREHOUSE_CITY"', NATIVE_TYPE 'VARCHAR2'),
        WAREHOUSE_STATE_PROVINCE string(30) OPTIONS (NAMEINSOURCE '"WAREHOUSE_STATE_PROVINCE"', NATIVE_TYPE 'VARCHAR2'),
        WAREHOUSE_POSTAL_CODE string(30) OPTIONS (NAMEINSOURCE '"WAREHOUSE_POSTAL_CODE"', NATIVE_TYPE 'VARCHAR2'),
        WAREHOUSE_COUNTRY string(30) OPTIONS (NAMEINSOURCE '"WAREHOUSE_COUNTRY"', NATIVE_TYPE 'VARCHAR2'),
        WAREHOUSE_OWNER_NAME string(30) OPTIONS (NAMEINSOURCE '"WAREHOUSE_OWNER_NAME"', NATIVE_TYPE 'VARCHAR2'),
        WAREHOUSE_PHONE string(30) OPTIONS (NAMEINSOURCE '"WAREHOUSE_PHONE"', NATIVE_TYPE 'VARCHAR2'),
        WAREHOUSE_FAX string(30) OPTIONS (NAMEINSOURCE '"WAREHOUSE_FAX"', NATIVE_TYPE 'VARCHAR2')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."WAREHOUSE"', UPDATABLE TRUE, CARDINALITY 24);

CREATE FOREIGN TABLE WAREHOUSE_CLASS (
        WAREHOUSE_CLASS_ID bigdecimal(22) NOT NULL OPTIONS (NAMEINSOURCE '"WAREHOUSE_CLASS_ID"', NATIVE_TYPE 'NUMBER'),
        DESCRIPTION string(30) OPTIONS (NAMEINSOURCE '"DESCRIPTION"', NATIVE_TYPE 'VARCHAR2')
) OPTIONS (NAMEINSOURCE '"FOODMART".""."WAREHOUSE_CLASS"', UPDATABLE TRUE, CARDINALITY 6);
Actions
3. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 1, 2017 2:43 PM (in response to ichanjasper)

Here is the information that I got from calling TeiidEmbeddedServer.printVDB("311741836")

311741836.1[jasperserver_FOODMART{1017993016=1017993016, oracle, 1017993016}]
Actions
4. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 1, 2017 4:33 PM (in response to ichanjasper)

Here is the model metadata. I put importer.schemaPattern = "FOODMART", importertrimColumnNames="true", and importer.useullSchemaName="false".
Actions
5. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 1, 2017 4:37 PM (in response to ichanjasper)

Not sure how "FOOTMART" can translate to "FOODMART"."" . But it works in Teiid version 8.12.3 against the same oracle DB.
Actions
6. Re: Oracle: wrong query generated for oracle 12c

shawkins Mar 1, 2017 7:59 PM (in response to ichanjasper)

What driver are you using? It looks like it's returning an empty string for the catalog. The code currently expects a non-null to indicate the package name.
Actions
7. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 2, 2017 12:32 PM (in response to shawkins)

We are using progress oracle JDBC driver 5.1.4.
Actions
8. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 2, 2017 12:53 PM (in response to ichanjasper)

It also doesn't work with Oracle 11g. I will try with native oracle JDBC driver shortly. Thanks.
Actions
9. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 2, 2017 12:59 PM (in response to ichanjasper)

It works with Oracle Native JDBC driver, but it doesn't work Oracle progress JDBC driver. Is there any workaround for this issue for the customers who is using progress JDBC driver? Thanks a lot.
Actions
10. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 2, 2017 2:45 PM (in response to ichanjasper)

And I got a similar problem with Vertica using Veritca native JDBC drver, vertica-jdbc-7.2.1-0 when using Teiid version 9.1.1. And it works ok with Teiid version 8.12.3.

It mapped to incorrect table name,"dbadmin"."store"."store_dimension", instead of ."store"."store_dimension". Should I file a bug for this issue?

VDB DLL file:

CREATE FOREIGN TABLE store_dimension (
        store_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"store_key"', NATIVE_TYPE 'Integer'),
        store_name string(64) DEFAULT '' OPTIONS (NAMEINSOURCE '"store_name"', NATIVE_TYPE 'Varchar'),
        store_number long DEFAULT '' OPTIONS (NAMEINSOURCE '"store_number"', NATIVE_TYPE 'Integer'),
        store_address string(256) DEFAULT '' OPTIONS (NAMEINSOURCE '"store_address"', NATIVE_TYPE 'Varchar'),
        store_city string(64) DEFAULT '' OPTIONS (NAMEINSOURCE '"store_city"', NATIVE_TYPE 'Varchar'),
        store_state string(2) DEFAULT '' OPTIONS (NAMEINSOURCE '"store_state"', NATIVE_TYPE 'Char'),
        store_region string(64) DEFAULT '' OPTIONS (NAMEINSOURCE '"store_region"', NATIVE_TYPE 'Varchar'),
        floor_plan_type string(32) DEFAULT '' OPTIONS (NAMEINSOURCE '"floor_plan_type"', NATIVE_TYPE 'Varchar'),
        photo_processing_type string(32) DEFAULT '' OPTIONS (NAMEINSOURCE '"photo_processing_type"', NATIVE_TYPE 'Varchar'),
        financial_service_type string(32) DEFAULT '' OPTIONS (NAMEINSOURCE '"financial_service_type"', NATIVE_TYPE 'Varchar'),
        selling_square_footage long DEFAULT '' OPTIONS (NAMEINSOURCE '"selling_square_footage"', NATIVE_TYPE 'Integer'),
        total_square_footage long DEFAULT '' OPTIONS (NAMEINSOURCE '"total_square_footage"', NATIVE_TYPE 'Integer'),
        first_open_date date DEFAULT '' OPTIONS (NAMEINSOURCE '"first_open_date"', NATIVE_TYPE 'Date'),
        last_remodel_date date DEFAULT '' OPTIONS (NAMEINSOURCE '"last_remodel_date"', NATIVE_TYPE 'Date'),
        number_of_employees long DEFAULT '' OPTIONS (NAMEINSOURCE '"number_of_employees"', NATIVE_TYPE 'Integer'),
        annual_shrinkage long DEFAULT '' OPTIONS (NAMEINSOURCE '"annual_shrinkage"', NATIVE_TYPE 'Integer'),
        foot_traffic long DEFAULT '' OPTIONS (NAMEINSOURCE '"foot_traffic"', NATIVE_TYPE 'Integer'),
        monthly_rent_cost long DEFAULT '' OPTIONS (NAMEINSOURCE '"monthly_rent_cost"', NATIVE_TYPE 'Integer'),
        CONSTRAINT C_PRIMARY PRIMARY KEY(store_key)
) OPTIONS (NAMEINSOURCE '"dbadmin"."store"."store_dimension"', UPDATABLE TRUE);

CREATE FOREIGN TABLE store_orders_fact (
        product_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"product_key"', NATIVE_TYPE 'Integer'),
        product_version long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"product_version"', NATIVE_TYPE 'Integer'),
        store_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"store_key"', NATIVE_TYPE 'Integer'),
        vendor_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"vendor_key"', NATIVE_TYPE 'Integer'),
        employee_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"employee_key"', NATIVE_TYPE 'Integer'),
        order_number long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"order_number"', NATIVE_TYPE 'Integer'),
        date_ordered date DEFAULT '' OPTIONS (NAMEINSOURCE '"date_ordered"', NATIVE_TYPE 'Date'),
        date_shipped date DEFAULT '' OPTIONS (NAMEINSOURCE '"date_shipped"', NATIVE_TYPE 'Date'),
        expected_delivery_date date DEFAULT '' OPTIONS (NAMEINSOURCE '"expected_delivery_date"', NATIVE_TYPE 'Date'),
        date_delivered date DEFAULT '' OPTIONS (NAMEINSOURCE '"date_delivered"', NATIVE_TYPE 'Date'),
        quantity_ordered long DEFAULT '' OPTIONS (NAMEINSOURCE '"quantity_ordered"', NATIVE_TYPE 'Integer'),
        quantity_delivered long DEFAULT '' OPTIONS (NAMEINSOURCE '"quantity_delivered"', NATIVE_TYPE 'Integer'),
        shipper_name string(32) DEFAULT '' OPTIONS (NAMEINSOURCE '"shipper_name"', NATIVE_TYPE 'Varchar'),
        unit_price long DEFAULT '' OPTIONS (NAMEINSOURCE '"unit_price"', NATIVE_TYPE 'Integer'),
        shipping_cost long DEFAULT '' OPTIONS (NAMEINSOURCE '"shipping_cost"', NATIVE_TYPE 'Integer'),
        total_order_cost long DEFAULT '' OPTIONS (NAMEINSOURCE '"total_order_cost"', NATIVE_TYPE 'Integer'),
        quantity_in_stock long DEFAULT '' OPTIONS (NAMEINSOURCE '"quantity_in_stock"', NATIVE_TYPE 'Integer'),
        reorder_level long DEFAULT '' OPTIONS (NAMEINSOURCE '"reorder_level"', NATIVE_TYPE 'Integer'),
        overstock_ceiling long DEFAULT '' OPTIONS (NAMEINSOURCE '"overstock_ceiling"', NATIVE_TYPE 'Integer'),
        CONSTRAINT fk_store_orders_store FOREIGN KEY(store_key) REFERENCES store_dimension (store_key)
) OPTIONS (NAMEINSOURCE '"dbadmin"."store"."store_orders_fact"', UPDATABLE TRUE);

CREATE FOREIGN TABLE store_sales_fact (
        date_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"date_key"', NATIVE_TYPE 'Integer'),
        product_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"product_key"', NATIVE_TYPE 'Integer'),
        product_version long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"product_version"', NATIVE_TYPE 'Integer'),
        store_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"store_key"', NATIVE_TYPE 'Integer'),
        promotion_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"promotion_key"', NATIVE_TYPE 'Integer'),
        customer_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"customer_key"', NATIVE_TYPE 'Integer'),
        employee_key long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"employee_key"', NATIVE_TYPE 'Integer'),
        pos_transaction_number long NOT NULL DEFAULT '' OPTIONS (NAMEINSOURCE '"pos_transaction_number"', NATIVE_TYPE 'Integer'),
        sales_quantity long DEFAULT '' OPTIONS (NAMEINSOURCE '"sales_quantity"', NATIVE_TYPE 'Integer'),
        sales_dollar_amount long DEFAULT '' OPTIONS (NAMEINSOURCE '"sales_dollar_amount"', NATIVE_TYPE 'Integer'),
        cost_dollar_amount long DEFAULT '' OPTIONS (NAMEINSOURCE '"cost_dollar_amount"', NATIVE_TYPE 'Integer'),
        gross_profit_dollar_amount long DEFAULT '' OPTIONS (NAMEINSOURCE '"gross_profit_dollar_amount"', NATIVE_TYPE 'Integer'),
        transaction_type string(16) DEFAULT '' OPTIONS (NAMEINSOURCE '"transaction_type"', NATIVE_TYPE 'Varchar'),
        transaction_time time DEFAULT '' OPTIONS (NAMEINSOURCE '"transaction_time"', NATIVE_TYPE 'Time'),
        tender_type string(8) DEFAULT '' OPTIONS (NAMEINSOURCE '"tender_type"', NATIVE_TYPE 'Varchar'),
        CONSTRAINT fk_store_sales_store FOREIGN KEY(store_key) REFERENCES store_dimension (store_key)
) OPTIONS (NAMEINSOURCE '"dbadmin"."store"."store_sales_fact"', UPDATABLE TRUE);
Actions
11. Re: Oracle: wrong query generated for oracle 12c

shawkins Mar 2, 2017 3:38 PM (in response to ichanjasper)

This isn't being reported as a regression correct? I don't think anything changed in the Vertica logic.

I don't see in the Vertica docs the naming convention of .schema.table. It looks like db.schema.table should be valid -https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/INSERT.htm%3FTocPath%3DSQL%2520…

Can you point me to the docs for this?
Actions
12. Re: Oracle: wrong query generated for oracle 12c

shawkins Mar 2, 2017 3:42 PM (in response to ichanjasper)

On the oracle issue there doesn't appear to be a good workaround as the logic is just checking for null and not for empty string. That will need to be addressed by an issue. The behavioral change was introduced by [TEIID-3870] Add ability to properly qualify package objects - JBoss Issue Tracker in 8.12.4
Actions
13. Re: Oracle: wrong query generated for oracle 12c

ichanjasper Mar 2, 2017 6:28 PM (in response to shawkins)

Should I file a bug for the oracle issue that we discussed? I am still working on the vertica issue. It maybe causes by another thing. Thanks.
Actions
14. Re: Oracle: wrong query generated for oracle 12c

shawkins Mar 3, 2017 5:00 PM (in response to ichanjasper)

> Should I file a bug for the oracle issue that we discussed?

Yes, please do. The change will be simple.
Actions

1 2 Previous Next

Go to original post