Recursive Query - Cannot Have Aliases Whose Names Matches Recursive Name

General
N/A

Recursive Query - Cannot Have Aliases Whose Names Matches Recursive Name

Hello

I'm trying to run this recursive query but it's giving me an error:

WITH RECURSIVE TSD_Candidate_Treatment
(
CandidateID
, RuleSetSeqNum
, RuleSetID
, RuleSetResultInd
, NextRuleSetSeqNum
, Treatment
, IP_Ind
, Step_Order
, Step_Flow
)
AS
(
SELECT
a.CandidateID
, a.RuleSetSeqNum
, a.RuleSetID
, a.RuleSetResultInd
, a.NextRuleSetSeqNum
, a.Treatment
, a.IP_Ind
, CAST(0 AS INTEGER) AS Step_Order
, CAST(a.RuleSetID AS VARCHAR(1000)) AS Step_Flow
FROM MyDB.TSD_Candidate_Stage AS a
WHERE RuleSetSeqNum = 1

UNION ALL

SELECT
b.CandidateID
, b.RuleSetSeqNum
, b.RuleSetID
, b.RuleSetResultInd
, b.NextRuleSetSeqNum
, b.Treatment
, b.IP_Ind
, a.Step_Order + 1
, a.Step_Flow || ',' || b.RuleSetID || CASE WHEN b.Treatment IS NULL THEN '' ELSE b.RuleSetResultInd END AS Step_Flow

FROM TSD_Candidate_Treatment AS a

INNER JOIN MyDB.TSD_Candidate_Stage AS b
ON a.CandidateID = b.CandidateID
AND a.NextRuleSetSeqNum = b.RuleSetSeqNum

WHERE a.Treatment IS NULL
)
SELECT *
FROM TSD_Candidate_Treatment
WHERE a.Treatment IS NOT NULL
ORDER BY CandidateID, Step_Order;

The error is:

Objects referenced in a recursive query/view may not have aliases whose name match the recursive name or its aliases.

I cannot figure this out... is someone able to point me in the right direction?

Cheers

Tim

Tags (4)
1 REPLY

Re: Recursive Query - Cannot Have Aliases Whose Names Matches Recursive Name

...

SELECT *

FROM TSD_Candidate_Treatment

WHERE a.Treatment IS NOT NULL   <-- This 'a.' reference is wrong

ORDER BY CandidateID, Step_Order;

 

Cheers.

 

Carlos.