topic Not able to save CTE as Table or View in Fabric platform

Not able to save CTE as Table or View

lovishsood1 — Fri, 03 Nov 2023 13:53:24 GMT

Hello Everyone,

I have created a CTE as follows;

WITH cte as ( SELECT Month_Name, Month_Number, Project_Category, 100.0 * Actual_Hours / NULLIF(SUM(Actual_Hours) OVER (PARTITION BY Month_Name, Month_Number), 0) AS Percentage FROM [DW_MonthlyReport].[dbo].[TimelogByType] --GROUP by Month_Name,Month_Number,Project_Category ) select cte.Month_Name, cte.Month_Number, cte.Project_Category,Sum(cte.Percentage) AS Percentage from cte GROUP by cte.Month_Name,cte.Month_Number,cte.Project_Category;

When I'm trying to save it as View of Table.

I'm getting this error;

What I'm doing wrong Please tell.

Re: Not able to save CTE as Table or View

Anonymous — Fri, 03 Nov 2023 14:38:36 GMT

Hi lovishsood1 ,
Thanks for using Fabric Community.
Apologies for the issue you have been facing.
I tried to repro the scenario. I have a table named Allotment in my warehouse and I performed a T-SQL operation as follows:

I did not receive any error here.

Try adding a ; before your WITH clause.

Start your query like this ; WITH cte as ....

Hope this helps. Please let me know if you have any further issues. Glad to help.

Re: Not able to save CTE as Table or View

AndyDDC — Fri, 03 Nov 2023 14:42:54 GMT

Can you post the whole SQL script you're using the create the view? eg

create view dbo.vwGetData

AS

with datav3

AS

(

    select * from fact_sales

)

select * from datav3

Re: Not able to save CTE as Table or View

lovishsood1 — Fri, 03 Nov 2023 17:20:21 GMT

Hi,

Thank you the reply.

But Output is perfectly being shown.
Problem is coming when I try to save the OUTPUT as VIEW of Save it as the table.

Re: Not able to save CTE as Table or View

lovishsood1 — Fri, 03 Nov 2023 17:21:05 GMT

This is the entire script.

Re: Not able to save CTE as Table or View

lovishsood1 — Fri, 03 Nov 2023 17:31:04 GMT

I want to save this SCRIPT as TABLE so that i can use the OUTPUT in Power BI.

; WITH cte AS ( SELECT Month_Name, Month_Number, Project_Category, 100.0 * Actual_Hours / NULLIF(SUM(Actual_Hours) OVER (PARTITION BY Month_Name, Month_Number), 0) AS Percentage FROM [DW_MonthlyReport].[dbo].[TimelogByType] -- GROUP by Month_Name,Month_Number,Project_Category ) SELECT cte.Month_Name, cte.Month_Number, cte.Project_Category, SUM(cte.Percentage) AS Percentage FROM cte GROUP BY cte.Month_Name, cte.Month_Number, cte.Project_Category;

Re: Not able to save CTE as Table or View

Anonymous — Fri, 03 Nov 2023 17:56:53 GMT

Hi lovishsood1 ,
Thanks for the input.
Apologies for the issue you have been facing. The option for SAVE AS TABLE should not be available in the Fabric Warehouse SQL endpoint UI . SQL endpoint in Warehouse is read only for tables. Sorry for the inconvenience.

I have raised a bug on the same and escalated it to the internal team. The option should be removed from the Warehouse SQL endpoint. I will keep you posted regarding the updates. Appreciate your patience.

Hope this helps. Please let me know if you have any further questions.

Re: Not able to save CTE as Table or View

lovishsood1 — Fri, 03 Nov 2023 18:01:04 GMT

I cannot save this as VIEW also?
How can I achieve this output in Power Query Editor so that i can calculate Each Project Category Hours for each month?

Formula :
(Actual Hours for Project Category / Tota Hours for that month)*100

You can check the QUERY SNAPSHOT also.

Re: Not able to save CTE as Table or View

HimanshuS-msft — Fri, 03 Nov 2023 18:03:14 GMT

Hello lovishsood1
When you say "I want to save this SCRIPT as TABLE " , I am asuming that the intend is just to have these rows on some report , please correct me if I am wrong . I think we can use the view route for this for now.

Since you have a CTE which you are running and i suggest you can create a view using the the CTE ( AndyDDC was also pointing that out ) and the use that view to get the data in the report . I have some dummy data with which i tried and it worked .

Create TABLE TimelogByType

(Month_Name varchar(100) ,Month_Number int ,Project_Category varchar(100) , Actual_Hours int )

INSERT INTO TimelogByType values ('Jan',202301,'AAA',8)

INSERT INTO TimelogByType values ('Jan',202301,'AAA',18)

INSERT INTO TimelogByType values ('Feb',202302,'BBB',118)

INSERT INTO TimelogByType values ('Feb',202302,'CCC',1118)

CREATE view dbo.vwGetData

WITH cte as

(

SELECT

Month_Name,

Month_Number,

Project_Category,

100.0 * Actual_Hours / NULLIF(SUM(Actual_Hours) OVER (PARTITION BY Month_Name, Month_Number), 0) AS Percentage

FROM

[dbo].[TimelogByType]

)

select cte.Month_Name,

cte.Month_Number,

cte.Project_Category,Sum(cte.Percentage) AS Percentage from cte GROUP by cte.Month_Name,cte.Month_Number,cte.Project_Category;

select * from dbo.vwGetData

You can also call this a inn powerBI report as this .

let me know if you have any question .

Thanks
Himanshu

Re: Not able to save CTE as Table or View

AndyDDC — Fri, 03 Nov 2023 18:25:46 GMT

if you want to create a view, just put the following syntax before your WITH statement:

CREATE VIEW dbo.viewname

WITH...

Re: Not able to save CTE as Table or View

lovishsood1 — Fri, 03 Nov 2023 18:28:06 GMT

I think this worked but problem is

I'm still not able to resolve what I actually I want to achieve.

This should be my end output:

But the Problem is My data is coming out of 100% and Above Output is showing DATA according to Total Hours for that month.

I hope I'm able to describe my problem statement.

Current Table :

Output Table :

I want to achieve like this so that I can create the graph as shown above.

You can ignore the Month Column of Output table as I'm okay with using Month Name & Month Number column as it is.

I hope my problem is clear.

Script I'm using I'm already mentioned above.

Re: Not able to save CTE as Table or View

AndyDDC — Fri, 03 Nov 2023 18:34:39 GMT

I think this is now a Power BI issue rather than a Synapse issue. You will be better served posting this logic issue in the Power BI forum

Re: Not able to save CTE as Table or View

Jack_Sullivan — Wed, 26 Feb 2025 18:23:18 GMT

Hi Himanshu,

I am getting the same error as above. But I cannot fix it with the solution you have presented.

My code is below and it runs perfectly when I hit run on the SQL analytics endpoint.

However, when I select the code and hit "save as view" I get the following error "Incorrect Syntax near the keyword 'WITH'"(same error as above)

My code is as follows:

--gets interval value with latest ReadingReplacementVersionNumber for each mprn and timestamp

--matches datetime of interval value to datetime of prices, then calculates credit for each period for each mprn

--returns export and credit grouped by mprn and generation month

WITH latest_export AS (

SELECT

MPRN,

IntervalPeriodTimestamp,

MAX(ReadingReplacementVersionNumber) AS MaxVersion

FROM ELEC_MM_Tables.TABLE_344

GROUP BY MPRN, IntervalPeriodTimestamp

export_prices_merged AS (

SELECT

export.MPRN,

export.IntervalPeriodTimestamp,

export.IntervalValue / 2 AS IntervalValue_kWh, -- Converting kW to kWh

export.ReadingReplacementVersionNumber,

BM_prices.IMBALANCE_SETTLEMENT_PRICE,

(export.IntervalValue / 2 * BM_prices.IMBALANCE_SETTLEMENT_PRICE/1000) AS Credit_Eur -- prices in €/MWh, export in kW. Dividing by 2 for kW - kWh and by 1000 for kWh - MWh

FROM ELEC_MM_Tables.TABLE_344 AS export

INNER JOIN latest_export le

ON export.MPRN = le.MPRN

AND export.IntervalPeriodTimestamp = le.IntervalPeriodTimestamp

AND export.ReadingReplacementVersionNumber = le.MaxVersion

LEFT JOIN dbo.ISP_PRICING BM_prices

ON export.IntervalPeriodTimestamp = BM_prices.DATE_TIME

)

SELECT

MPRN,

FORMAT(IntervalPeriodTimestamp, 'yyyy-MM') AS Month, -- Group by Month

ROUND(SUM(IntervalValue_kWh),3) AS Total_Export_kWh,

ROUND(SUM(Credit_Eur),3) AS Total_Credit_Eur,

MIN(IntervalPeriodTimestamp) as Minimum_Read_Date_In_Month,

MAX(IntervalPeriodTimestamp) as Maximum_Read_Date_In_Month

FROM export_prices_merged

GROUP BY MPRN, FORMAT(IntervalPeriodTimestamp, 'yyyy-MM')

ORDER BY MPRN, Month

Re: Not able to save CTE as Table or View

hernandezpaulms — Tue, 27 May 2025 13:47:33 GMT

Your problem is the semicolon at the end of the statement. Delete it and the view will be created.