All Forums General
turgs 3 posts Joined 08/10
28 Apr 2014
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
 
 

CarlosAL 512 posts Joined 04/08
28 Apr 2014

...

SELECT *

FROM TSD_Candidate_Treatment

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

ORDER BY CandidateID, Step_Order;

 

Cheers.

 

Carlos.

You must sign in to leave a comment.