>>It is different if you got a 200+ line SQL statement where serveral CTEs with fuzzy definitions are at the top and are using it two pages down. The novice reader will look at a statement
>>
>>

>>SELECT SomefieldsGodknowsWhereTheyWereDefined, ......
>>      FROM Cte_1
>>            LEFT JOIN Cte_2 ON .....
>>            INNER JOIN Cte_3 ON .... (Trying to figure out all where the fields in here are defined)
>>     WHERE ... (Again trying to figure out where all the fields are coming from and what the expression really means)
>>

>
>As if it would too easy to do the same with subqueries, you instead get lost reading subqueries going to bottom and then try to climb up to see the main. With CTE it is just readable, you read from top to bottom and you see where things are coming from. No scroll up as you claim, already reading from top to bottom, maybe you meant subqueries when saying "scroll up and down", that would be exactly the case with subqueries :) You really make up things as you see fit, put constraints where you don't like.


Totally disagree with that. Nope, nope, nope. With subquereis, the details are where you expect them and you can debug and maintain them exactly where they are used. With CTE's the place where you define them and actually use them could be pages apart. There is not denying that.

CTE's are just readable is just a myth in many cases. It is only readable if the CTE definition is well defined entity that has meaning in the real world, which would bring up the argument why not creating a view or inline function. In practise we see CTEs that have fuzzy descriptions and whenever you use them you'll have to combine both the Query and the CTE in your head to figure out what is going on.

>>Again, you don't seem to realize that the repitions are refering to type 2 CTEs, of which I'm far more sympathetic than single use CTEs. So I did not constrain them away.
>
>Really? You didn't constraint away queries with repetitions? I thought you did along with recursive queries. Then what we are discussing at all. That alone shows a much more readable query.

Let me remind you, you engaged the discussion when I said that CTE's were overused. I eplained exactly why I think so, based upon the query you gave at the beginning of the thread. Overused does not mean they are without value. Overused means that they are even used in situations where it better not to use them and use an alternative and that included subqueries.

I don't get the fuss about why subqueries are unreadable. You read them top to bottom and the details are exactly where they are executed while with CTE's the definition and usages could be pages apart. Happy debugging.

>Debugging or optimization do not constitute a problem for CTEs, you are just making that up.

Nope. Been there, done that. While there technically is no problem, its a problem that the definition and execution are in two different places. When dealing with complex queries it will get more difficult to get your head arround it.

Sorry, but you won't convince me on that one. We will have to agree to disagree here.