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...