循环浏览记录历史以查找所需记录
|
我是CTE的新手，我正在努力弄清楚这一点。
我有一个存储所有合同的表，我拥有最新合同“合同A”的记录，并且我想按历史顺序浏览相关合同，以查找具有特定条件的最新合同，有时它将是我已经拥有的合同。有时深一层，也许多达五层。我建立了一个CTE，它将记录限制为与\“ Contract A \”关联的记录，但是它陷入无限循环，SQL杀死了它，顺便说一句，我正在使用SQL Server 2008
这是我的代码：
CREATE TABLE #t_Contracts
( ContractOID INT NOT NULL
, SystemSourceOID SMALLINT NOT NULL
)
--\"Contract A\"
INSERT #t_Contracts
SELECT 7463027, 2
WITH Contract_CTE (ContractOID, SystemSourceOID, PrevContractOID, ProductCodeType,Comment, Contractlevel)
AS
(
--Anchor with \"Contract A\"
SELECT CON.ContractOID, CON.SystemSourceOID, CON.PrevContractOID, PRD.ProductCodeType, PRD.Comment, 0 AS Contractlevel
FROM UBASS.dbo.[Contract] AS CON
INNER JOIN #t_Contracts AS TMP ON CON.ContractOID = TMP.ContractOID
AND CON.SystemSourceOID = TMP.SystemSourceOID
INNER JOIN UBASS.dbo.Product AS PRD ON CON.ProductOID = PRD.ProductOID
AND CON.SystemSourceOID = PRD.SystemSourceOID
UNION ALL
--\"Contract A\" Previous contracts
SELECT CON.ContractOID, CON.SystemSourceOID, CON.PrevContractOID, PRD.ProductCodeType, PRD.Comment, Contractlevel + 1
FROM UBASS.dbo.[Contract] AS CON
INNER JOIN Contract_CTE AS pCON ON pCON.PrevContractOID = CON.ContractOID
AND CON.SystemSourceOID = pCON.SystemSourceOID
INNER JOIN UBASS.dbo.Product AS PRD ON CON.ProductOID = PRD.ProductOID
AND CON.SystemSourceOID = PRD.SystemSourceOID
)
SELECT * FROM Contract_CTE
WHERE
ProductCodeType NOT IN (\'MPP\',\'RBP\',\'STP\',\'RCP\',\'BNE\')
AND Comment != \'RETENTION\'
--WHERE ContractOID = 7463027 AND SystemSourceOID = 2
没有找到相关结果
已邀请:
1 个回复
填盖