Please advise if anyone experience similar error as below in 19c:. Works fine in earlier version 11g.
[Error] Execution (471: 1): ORA-12801: error signaled in parallel query server P00A, instance xvu11dbm08.:TCDBGQ178 (8)
ORA-32034: unsupported use of WITH clause
WITH
AppliedFxRates_CTE AS
(
SELECT /*+ materialize */
fx.CurrencyUID
,fx.Rate
FROM
Results.FxRate fx
WHERE
fx.ValuationUID = 16955
AND fx.ValueTypeUID = 10158
),
PathCoverage_CTE AS
(
SELECT DISTINCT
agg.TradeUID,
MAX(NVL(NumPaths,-1)) as TradePathCoverage
FROM
Results.TradeAgg agg
LEFT OUTER JOIN
Results.TradeExclusion excl
ON
excl.ValuationUID = 16980
AND excl.TradeUID = agg.TradeUID
WHERE
agg.ValuationUID = 16980
AND excl.TradeUID IS
NULL
GROUP BY agg.TradeUID
),
TradeData_CTE AS
(
SELECT bm.MPUShortName AS BusGrp,
bm.CBUShortName AS BusType,
bm.SPUName AS DeskGrp,
bm.BUName AS Desk,
--
p.FullName AS Portfolio,
p.BookMapID,
PG.ShortName AS ProdGrp,
PDet.ShortName AS ProdDetail,
t.TradeUID,
TSrc.FullName AS TrdSrc,
t.TradeIdentifier AS TradeID,
(t.TradeCurrentNotional * TFX.Rate) AS TradeNotlUSD,
pc.TradePathCoverage
FROM
Results.TradeDetail t
LEFT OUTER JOIN
AppliedFxRates_CTE TFx
ON TFx.CurrencyUID = t.CurrencyUID
INNER JOIN
Results.CodeLegend TSrc
ON
TSrc.CodeLegendUID = t.SourceSystemUID
INNER JOIN
Results.ProductDetail PDet
ON PDet.ProductDetailUID =
t.ProductDetailUID
INNER JOIN
Results.ProductGroup
PG
ON PG.ProductGroupUID = PDet.ProductGroupUID
INNER JOIN
Results.Portfolio p
ON
p.PortfolioUID = t.PortfolioUID AND p.IsMostRecent = 1
INNER
JOIN
Results.BookMap bm
ON p.BookMapID =
bm.BookMapID AND bm.IsMostRecent = 1
LEFT OUTER JOIN
Results.TradeExclusion excl
ON excl.ValuationUID = 16980
AND excl.TradeUID = t.TradeUID
LEFT OUTER JOIN --DO NOT CHANGE
--FROM LEFT OUTER JOIN. This is needed to show differences between # Base Trades
--and # Path Trades.
PathCoverage_CTE pc
ON
pc.TradeUID = t.TradeUID
----Portfolio filter----
INNER JOIN
TMP_CRMComponent_Portfolio port
ON t.PortfolioUID = port.FullName
----ProductDetail filter----
INNER JOIN TMP_CRMComponent_ProductDetail pd
ON t.ProductDetailUID = pd.FullName
----Restricted Portfolio filter included in main Portfolio filter----
WHERE
t.RevalDate = '10-DEC-2020'
AND excl.TradeUID IS NULL
),
TradeLegData_CTE AS
(
SELECT
T.TradeUID,
TL.TradeLegUID,
TLFx.Rate TLFxrate,
TLFx.CurrencyUID
FROM
TradeData_CTE T
INNER JOIN
Results.TradeLeg TL
ON TL.TradeUID = T.TradeUID
INNER JOIN
Results.LegType LT
ON LT.LegTypeUID = TL.LegTypeUID
LEFT OUTER JOIN
AppliedFxRates_CTE TLFx
ON TLFx.CurrencyUID = TL.CurrencyUID
WHERE
LT.IsFee = 0
),
EvolvedFX_CTE AS
(
SELECT PathUID, CurrencyUID, Rate
FROM
Results.FxRatePath
WHERE ValuationUID = 16980
),
BaseTradeData_CTE AS
(
SELECT TL.TradeUID,
SUM(sens.PV * TL.TLFXRate) SpotBasePVUSD,
SUM(sens.PV * FX.Rate) EvolvedBasePVUSD
FROM
TradeLegData_CTE TL ----ALL NON-FEE LEGS
INNER JOIN
Results.PathSens Sens
ON Sens.TradeLegUID = TL.TradeLegUID
INNER JOIN
EvolvedFX_CTE FX
ON
fx.CurrencyUID = TL.CurrencyUID AND
fx.PathUID = 0
WHERE Sens.ValuationUID = 16980 AND
Sens.PathUID = 0
GROUP BY TL.TradeUID
),
PathTradeData_CTE AS
(
SELECT TL.TradeUID,
PS.PathUID,
SUM(PS.PV * fx.Rate) AS EvolvedPathPVUSD,
SUM(PS.PV * TL.TLFXRate) AS SpotPathPVUSD
FROM
TradeLegData_CTE TL -- ALL NON-FEE LEGS
INNER JOIN
Results.PathSens PS
ON PS.TradeLegUID = TL.TradeLegUID
INNER JOIN
EvolvedFX_CTE fx
ON fx.CurrencyUID = TL.CurrencyUID AND
fx.PathUID = PS.PathUID
WHERE ps.ValuationUID = 16980
--
--PathFilter
----PathUID filter------
AND ps.PathUID IN (1)
GROUP BY TL.TradeUID, PS.PathUID ),
ReportDetail_CTE AS
(
SELECT
td.BusGrp,
td.BusType,
td.DeskGrp,
td.Desk,
td.Portfolio,
td.BookMapID,
td.ProdGrp,
td.ProdDetail,
td.TrdSrc,
path.PathUID,
td.TradeID,
ABS(td.TradeNotlUSD) AS AbsTradeNotlUSD,
NVL(path.EvolvedPathPVUSD, 0) - NVL(base.EvolvedBasePVUSD, 0) AS PathImpactUSDEvolvedFx,
NVL(path.SpotPathPVUSD, 0) - NVL(base.SpotBasePVUSD, 0) AS PathImpactUSDSpotFx,
base.EvolvedBasePVUSD AS BasePVUSD,
path.EvolvedPathPVUSD AS PathPVUSD /*,
CASE
WHEN base.TradeUID IS NOT NULL THEN 1
ELSE 0
END AS BaseNumTrades,
CASE
WHEN path.TradeUID IS NOT NULL THEN 1
ELSE 0
END AS PathNumTrades */
,
td.TradePathCoverage
FROM
--DO NOT CHANGE FROM LEFT
--OUTER JOIN. This is needed to show differences between # Base Trades and # Path
--Trades.
TradeData_CTE TD
LEFT OUTER JOIN
BaseTradeData_CTE base
ON base.TradeUID = TD.TradeUID
LEFT OUTER JOIN
PathTradeData_CTE path
ON
path.TradeUID = TD.TradeUID
WHERE COALESCE(base.TradeUID, path.TradeUID) IS NOT NULL
)
select * from ReportDetail_CTE
ORDER BY AbsTradeNotlUSD DESC, PathUID, TradeID