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.

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

Not able to save CTE as Table or View | Microsoft Fabric Community

13 Replies

Anonymous
Not applicable
2 years ago
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.
- lovishsood1
  Resolver I
  2 years ago
  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.
  - Anonymous
    Not applicable
    2 years ago
    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.
AndyDDC
Most Valuable Professional
2 years ago
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
- lovishsood1
  Resolver I
  2 years ago
  This is the entire script.
lovishsood1
Resolver I
2 years ago
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;
- HimanshuS-msft
  Microsoft Employee
  2 years ago
  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
  
  As
  
  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
  - lovishsood1
    Resolver I
    2 years ago
    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.
hernandezpaulms
Microsoft Employee
1 year ago
Your problem is the semicolon at the end of the statement. Delete it and the view will be created.