1
votes

I have the following code. I am unable to share the actual databases at this time. I cannot use CTEs in my current environment. How can I rewrite this query without using CTEs? Is there a better of doing this?

WITH OIL_RF

AS
(
SELECT  FldName,
[OOIP, 10^3bbl],
[RF, percent] AS 'Oil Recovery Factor %',
ResName

  FROM [A].[dbo].[B]
  WHERE ProjID NOT LIKE 'F%'
  AND RtechDEsc = 'PRMS'
  AND RclassDesc = '2P'
  AND Product = 'O'
 ),

OIIP_SUM
AS
(
SELECT  FldName,
SUM([OOIP, 10^3bbl]) AS 'Sum of OIIP per Field'

  FROM [A].[dbo].[B]
  WHERE ProjID NOT LIKE 'F%'
  AND RtechDEsc = 'PRMS'
  AND RclassDesc = '2P'
  AND Product = 'O'
 
GROUP BY FldName),

Weights

AS 
(

SELECT
OIL_RF.FldName, [OOIP, 10^3bbl],[Sum of OIIP per Field],ResName, [Oil Recovery Factor %],
case when [OOIP, 10^3bbl]=0 then NULL else [OOIP, 10^3bbl]/[Sum of OIIP per Field] end as 'Ratio of OIIP per Res and OIIP per Field (Weights)'

FROM OIL_RF LEFT JOIN OIIP_SUM ON OIL_RF.FldName= OIIP_SUM.FldName

),

RFbyWeights
AS 
(
SELECT *,
[Oil Recovery Factor %]*[Ratio of OIIP per Res and OIIP per Field (Weights)] AS 'RF*Weight'

FROM Weights
)


SELECT FldName AS 'Field Name',
SUM([RF*Weight]) AS 'Weighted RF %'
FROM RFbyWeights
GROUP BY FldName
What does “cannot use” mean? Is this a limitation imposed by a person, by a company standard, by you, by your tool? Any non-recursive CTE can be re-written as a subquery, but it gets cumbersome when you have multiple. What have you tried?Aaron Bertrand
I have to put this query in Spotfire. Spotfire doesn't support CTEs.Abramo
A CTE is just a form of sub-query, re-write your query to use nested sub-queries.Dale K