2 Replies Latest reply on Apr 22, 2015 4:31 PM by Ramesh Reddy

    Deleting column in federated table only updates half of SQL in union

    Brian Bek Jensen Newbie

      Using the example data that is used in RedHat training for Teiid.

       

      I have an Entrprise Data Layer with two customers tables, one from the US and one from the EU. I have changed the tables so they match the number of columns and the names are the same.

       

      In my federated data layer I created a new meta model and import the data from the EU Enterprise Data Layer model. I then select the US Enterprise Data Layer model and create a Union. All is well and the new federated table can show data.

       

      The table is named Customer in the Federated layer and one of the columns is named country.

      The SQL that creates the data looks like this:

       

      SELECT

        MiracleEUCustomer_EDL.customer.customerid, MiracleEUCustomer_EDL.customer.firstname, MiracleEUCustomer_EDL.customer.lastname, MiracleEUCustomer_EDL.customer.middlename, MiracleEUCustomer_EDL.customer.streetaddress, MiracleEUCustomer_EDL.customer.city, MiracleEUCustomer_EDL.customer.stateprovince, MiracleEUCustomer_EDL.customer.postalcode, MiracleEUCustomer_EDL.customer.country

      FROM

        MiracleEUCustomer_EDL.customer

      UNION ALL

      SELECT

        MiracleUSCustomer_EDL.customer.customerid, MiracleUSCustomer_EDL.customer.firstname, MiracleUSCustomer_EDL.customer.lastname, MiracleUSCustomer_EDL.customer.middlename, MiracleUSCustomer_EDL.customer.streetaddress, MiracleUSCustomer_EDL.customer.city, MiracleUSCustomer_EDL.customer.stateprovince, MiracleUSCustomer_EDL.customer.postalcode, MiracleUSCustomer_EDL.customer.country

      FROM

        MiracleUSCustomer_EDL.customer

       

      When I chose the button "Reconcile Transformation SQL with Target Columns" and remove the column "country" it only updates the SQL for the first Select statement. I have to manually remove the ", MiracleUSCustomer_EDL.customer.country" SQL part in the second to make the Union legal.

       

      Am I doing something wrong or is Teiid Designer unable to handle this situation? I would not like to present a potential client with a GUI tool that insists on you the user doing the stuff manually.

       

      Regards Brian