9 Replies Latest reply on Mar 22, 2017 8:19 AM by Steven Hawkins

    Teiid 9.1 Translator issue - Connecting to SQL 2014 for transformation query with 'union all'

    Chandra M Newbie

      Issue: VDB view which has 'Union All' in the transformation query causes issue or does not return data as expected. Happens only when the VDB connects to SQL 2014 behind. It does work, when connecting to SQL 2008 R2 DB.

       

       

      Details:

      Teiid version : 9.1 used to connect to VDB (Teiid 9.1 jars attached, zip)

      Connection URL : jdbc:teiid:<Name of VDB>@mm://<ip of server>:31000

       

       

      Editors tried for querying: Squirrel 3.7.1 (with same teiid jars attached as drivers) and Teiid 11.0 designer (Database development plugin) editor.

      View Definition: PPDMTx.ddl (attached)

       

       

      Command run: SELECT * FROM PPDMTx.PARENT_WELL_COMPL_PERCENT ORDER BY PARENT_WELL_COMPL_ID OFFSET 1 ROWS FETCH NEXT 100 ROWS ONLY

       

       

      Teiid Error:

      TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 PPDMSrc: 102 TEIID11008:TEIID11004 Error executing statement(s):

      [Prepared Values: [] SQL: SELECT OFFSET 1 ROWS FETCH NEXT 100 ROWS ONLY * FROM (SELECT g_7."PARENT_ID" AS c_0, g_7."WELL_ID" AS c_1, g_10."PARENT" AS c_2,

      g_9."WELL_NAME" AS c_3g_7."WELL_COMPONENT_ID" AS c_20 FROM "SQL2014DB"."dbo"."WELL_COMPONENT" g_7

      INNER JOIN "SQL2014DB"."dbo"."R_WELL_COMPONENT_TYPE" g_8 ON g_7."WELL_COMPONENT_TYPE" = g_8."R_WELL_COMPONENT_TYPE_ID"

      INNER JOIN "SQL2014DB"."dbo"."WELL" g_9 ON g_7."WELL_ID" = g_9."WELL_ID"

      INNER JOIN "SQL2014DB"."dbo"."PARENT" g_10 ON g_7."PARENT_ID" = g_10."PARENT_ID"

      WHERE g_7."ACTIVE_IND" = 'Y' AND g_8."WELL_COMPONENT_TYPE" = 'PARENT' AND g_9."ACTIVE_IND" = 'Y' AND g_10."ACTIVE_IND" = 'Y'

      UNION ALL

      SELECT g_1."PARENT_ID" AS c_0, g_0."COMPLETION_ID" AS c_1, g_3."PARENT" AS c_2, g_2."WELL_COMPLETION" AS c_3,

      g_0."COMPL_PARENT_PERCENT_ID" AS c_20 FROM "SQL2014DB"."dbo"."COMPL_PARENT_PERCENT" g_0

      INNER JOIN "SQL2014DB"."dbo"."COMPL_PARENT_PERCENT_COMP" g_1 ON g_0."COMPL_PARENT_PERCENT_ID" = g_1."COMPL_PARENT_PERCENT_ID"

      INNER JOIN "SQL2014DB"."dbo"."WELL_COMPLETION" g_2 ON g_0."COMPLETION_ID" = g_2."WELL_COMPLETION_ID"

      INNER JOIN "SQL2014DB"."dbo"."PARENT" g_3 ON g_1."PARENT_ID" = g_3."PARENT_ID"

        WHERE g_0."ACTIVE_IND" = 'Y' AND g_0."IS_ACTIVE" = 'Y' AND g_1."ACTIVE_IND" = 'Y' AND g_2."ACTIVE_IND" = 'Y' AND g_3."ACTIVE_IND" = 'Y') AS X ORDER BY c_20]

       

       

      Problem is the same ddl view works fine when connected behind with SQL 2008. Issue happens only when connected to SQL 2014 and

      when the view uses 'UNION ALL' in the transformation. The select statement has Offset information appended at the beginning.