recursive CTE in a view

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  August 14, 2013 at 5:28 am

  #151268

  I am trying to build this view with a cte and I am getting the error msg:

  Msg 252, Level 16, State 1, Procedure SubQryTradeDiscItemLevelSummary, Line 3

  Recursive common table expression 'TradeDiscDealLevelSummary' does not contain a top-level UNION ALL operator.

  code:

  Create View dbo.SubQryTradeDiscItemLevelSummary

  AS

  WITH SubQryTDAssignments AS

  (

  SELECT DISTINCT Include,Vndnbralt,Vndname,NumberofDeals,PotentialClaimAmt,Lastupdateddate,AssignedAuditors,Comments,Auditstatuscode,ID,RecordSelector

  FROM TradeDiscAssignments

  --WHERE AssignedAuditors Like '%" & GBL_Username & "%'

  ),

  TradeDiscDealLevelSummary AS

  (

  SELECT DISTINCT SubQryTDAssignments.RecordSelector AS REC, TradeDiscDealLevelSummary.*, SubQryTDAssignments.Comments

  FROM TradeDiscDealLevelSummary

  INNER JOIN SubQryTDAssignments

  ON TradeDiscDealLevelSummary.VndNbr = SubQryTDAssignments.Vndnbralt

  WHERE SubQryTDAssignments.RecordSelector = '-1'

  ),

  SubQryTradeDiscItemLevelSummary1 AS

  (

  SELECT DISTINCT SubQryTradeDiscDealLevelSummary.RecordSelector AS REC, TradeDiscItemLevelSummary.*, SubQryTradeDiscDealLevelSummary.Comments

  FROM TradeDiscItemLevelSummary

  INNER JOIN (TradeDiscDealLevelSummary

  INNER JOIN SubQryTradeDiscDealLevelSummary

  ON TradeDiscDealLevelSummary.TradeDiscDealLevelSummaryId = SubQryTradeDiscDealLevelSummary.TradeDiscDealLevelSummaryId)

  ON TradeDiscItemLevelSummary.TradeDiscDealLevelSummaryId = SubQryTradeDiscDealLevelSummary.TradeDiscDealLevelSummaryId

  WHERE SubQryTradeDiscDealLevelSummary.RecordSelector = '-1'

  )

  Select * From SubQryTradeDiscItemLevelSummary1

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 14, 2013 at 5:41 am

  #1641328

  Difficult to see what you're trying to do here, since your TradeDiscDealLevelSummary CTE attempts to select from itself.

  John

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  August 14, 2013 at 5:52 am

  #1641334

  If the query doesn't work, why try to construct a view out of it? Drop the CREATE VIEW part until you've got it working. It's just noise.

  Start again, your query doesn't make any sense at all. Start simple and build incrementally, testing at each step. Use table aliases.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden