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
