Forum Discussion

lovishsood1's avatar
lovishsood1
Icon for Resolver I rankResolver I
2 years ago
Solved

Not able to save CTE as Table or View

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(A...
Data Integration
Data Science
data warehouse
one lake
power bi
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    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

Anonymous's avatar
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's avatar
lovishsood1
Icon for Resolver I rankResolver 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's avatar
    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.

    • lovishsood1's avatar
      lovishsood1
      Icon for Resolver I rankResolver I
      2 years ago

      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.

  • AndyDDC's avatar
    AndyDDC
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

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

     

    CREATE VIEW dbo.viewname 

    AS

    WITH...