2

I just found an adequate solution to How to Find Rows which are Duplicates by a Key but Not Duplicates in All Columns?, coded the stored procedure, then learned that the database is stuck at SQL Server 2000.

My solution, of course, relies heavily on Common Table Expressions.

Can anyone provide me a set of ru les for converting back to the SQL Server 2000 dialect?

Note that I have things like thisL:

;
WITH CTE1 AS ( ... ),
CTE2 AS (SELECT ... FROM CTE1 ... ),
CTE3 AS (SELECT ... FROM CTE1 INNER JOIN CTE2 ...)
SELECT * FROM CTE3
WHERE criteria
ORDER BY sequence

This would appear to make things more interesting...

Update: None of the CTEs are recursive.

Community
  • 1
  • 1
John Saunders
  • 160,644
  • 26
  • 247
  • 397
  • John, I am sure it is just an oversight that none of the answers in your referenced post are marked as an answer. – RC_Cleland Nov 23 '10 at 02:26
  • @RC_Cleland: it's not an oversight. The post is from yesterday, and I haven't had a chance to try the suggestions. Deadlines are like that. For instance, this issue (2000 migration) came up at 17:08 yesterday - less than an hour before I had to leave for the day. – John Saunders Nov 23 '10 at 17:14
  • if you actually have a single query to convert it might be easier to do that as opposed to convert something generic, particularly if you have example source and result data for verification. – Samuel Neff Feb 18 '11 at 05:55

2 Answers2

6

Two options (granted, neither are pretty -- that's why we like CTE's)

OPTION 1

Create a temp table (#, or if small enough @) and refer to it as you would the CTE. Drop when you are done.

OPTION 2 Put the entire CTE SELECT as a table in the FROM portion of the query.

SELECT *
FROM  (SELECT *
       FROM table1) oldCTE
Brad
  • 15,361
  • 6
  • 36
  • 57
3

I don't think it is possible to come up with rules that would easily convert any cte into a non-cte statement. as the possibilities are too open-ended (particularly if you're working with recursive CTEs). The closest I can think of would be to take each CTE in order, break it into it's own query, and use it to populate a temporary table that's used by the following queries. Hardly efficient, and not guarnateed to work in all possible situations.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92