4 Replies Latest reply on Feb 13, 2013 11:55 AM by Samier Nadji

    CTE queries use view column names in SQL statements to data source

    Samier Nadji Newbie

      Hi,

       

      When my virtual view column names are different than the corresponding columns in the data source, I get errors using CTE queries.

       

      For example, I have a view:


      CREATE VIEW Task (

          TaskId          integer NOT NULL PRIMARY KEY,

          TaskDescription String,

          ...

      )   

      AS  select

          DB1Task.Id                  AS TaskId,

          DB1Task.Description    AS TaskDescription,

          ...

       

      As you can see above, the view "TaskId" column maps to the datasource "Id" column.  If I run a query such as:

       

      WITH My_CTE

      AS (SELECT TaskId, TaskDescription, ... FROM Task)

      SELECT ..., COUNT(My_CTE.TaskId) TaskCount

      FROM ...

       

      I get the following error - where it looks like Teiid is passing "TaskId" to the data source DB instead of "Id":

       

       

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

      [Prepared Values: [] SQL: WITH My_CTE AS (SELECT g_0."TaskId", g_0."UserId", g_0."UserId", g_0."ProjectId" FROM "DB1"."dbo"."Task" g_0) SELECT TOP 100 g_0."Name" AS c_0, COUNT(g_1.TaskId) AS c_1 FROM "DB1"."dbo"."Person" g_0, My_CTE g_1 WHERE g_1.UserId = g_0."UserId" GROUP BY g_0."Name"]

      SQLState:  TEIID30504

      ErrorCode: 0

       

       

      If I use the same column names in the view as they appear in the data source, the query works just fine. Is this a bug or did I miss something?

       

       

      Thanks