How to turn below CTE into SubQuery?

Question

I'm trying to convert this CTE into a Subquery, but I'm sort of a greenhorn on SQL. I have to do it because PowerBI Doesn't support CTEs, but I know it'd accept a subquery for direct querying. Can someone show me the correct way to do it? I hear Subqueries are also better coding practice?

    with cte as  
( 
       SELECT [Job #] 
       ,[GlCode] 
       ,[Date] 
       ,[Variance Amt] 
       ,[Job QTY] 
       ,[OpenQty] 
       ,[Part #] 
       ,[Material] 
       ,[PCS #] 
       ,[Matrl$$] 
       ,[Date Last Issue] 
       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
       ,[PO$$] 
       ,[Date Last Rcvd] 
       ,[Wip Total] 
       ,[per pc] 
       ,[Standard Cost] 
       ,[DIFF] 
       ,[% of Profit] 
       ,ROW_NUMBER() OVER(PARTITION BY [Job #] ORDER BY [Job #]) AS rn 
       ,count(*) over(partition by [Job #]) as maxrn 
       ,sum([Matrl$$]) over(partition by [Job #]) as [Job Matrl$$] 
  FROM [CompanyZ].[dbo].[WIPVarianceRptView] )
       SELECT  [Job #] 
       ,[GlCode] 
       ,[Date] 
       ,[Variance Amt] 
       ,[Job QTY] 
       ,[OpenQty] 
       ,[Part #] 
       ,[Material] 
       ,[PCS #] 
       ,[Matrl$$] 
       ,[Date Last Issue] 
       ,case when substring([PurchaseOrders],len([PurchaseOrders]),1) = '|' then substring([PurchaseOrders],1,len([PurchaseOrders])-1) else [PurchaseOrders] end [PurchaseOrders] 
       ,case when rn <> maxrn then 0 else [PO$$]           end as [PO$$] 
       ,[Date Last Rcvd] 
       ,case when rn <> maxrn then 0 when rn = maxrn then ([PO$$] + [Job Matrl$$]) else 0      end as [Wip Total] 
       ,case when rn <> maxrn then 0 else [per pc]         end as [per pc] 
       ,case when rn <> maxrn then 0 else [Standard Cost]  end as [Standard Cost] 
       ,case when rn <> maxrn then 0 else [DIFF]           end as [DIFF] 
       ,case when rn <> maxrn then 0 else [% of Profit]    end as [% of Profit] 
   FROM cte 
   Order By [Job #]

Stu Stu · Accepted Answer · 2022-07-28T15:53:04

The term you need in this instance is derived table, not sub-query.

You simply substitute the CTE definition into the from clause:

select [Job #], [GlCode] 
...
from (
    select [Job #], [GlCode] 
    ...
    from [CompanyZ].[dbo].[WIPVarianceRptView]
)cte 
order by [Job #];

I hear Subqueries are also better coding practice

Better than what? This is simply an opinion and not a citable fact, I would disregard wherever you read this.

How to turn below CTE into SubQuery?

1 Answers