MSSQL can't use CTEs - Githubissues

Describe the bug Using mssql when my cube sql contains a CTE, I can't perform aggregations.

The following is valid syntax:

WITH cte_employees AS (
   select employee from employee_table
)
SELECT count(*) FROM cte_employees

Cube generates, which isn't valid in mssql:

SELECT COUNT(employee_id) FROM ( 
   WITH cte_employees AS (
      select employee from employee_table
   )
 )

To Reproduce Steps to reproduce the behavior:

Add a model using the following

sql:  WITH cte_employees AS (
select employee from employee_table
)
select * from cte_employees

Expected behavior It should generate valid SQL for the driver.

Screenshots If applicable, add screenshots to help explain your problem.

Minimally reproducible Cube Schema In case your bug report is data modelling related please put your minimally reproducible Cube Schema here. You can use selects without tables in order to achieve that as follows.

cube(
  'cube_practitioners',

  {
    shown: true,

    sql: `
       with 

        select_columns as (
            select

                practitionerID as practitioner_pk,

            from tblPractitioner

        )
        select * from select_columns
        `,

    dimensions: {
      // join keys
      practitioner_pk: {
        sql: `${CUBE}.practitioner_pk`,
        type: `string`,
        primaryKey: true,
        shown: false
      }
    },

    measures: {
      count_practitioners: {
        type: `count`
      }
    }
  }
);

Version: 0.35.20

cube-js / cube

MSSQL can't use CTEs #8247