>>Again, Where did I state that I was against CTE's? Perhaps you should look up what the word "Over used" means. I think its now the 6th time I tried to explain that to you. Did I not say that I've written, many, many CTEs before ?? Did I not explain that imo CTE's are overused replacing SUBQUERIES ??

>Uh oh, it was your message stating you would never ever use CTEs. OK let's be it. Why in the hell it is over used???? Just because it is not written in the style you want ? (as a subquery)

Cetin, you are being dishonest here. You've taken my statement out of context. I would never use CTEs in a situation of a replacing a single subquery with a CTE (type 3) like you did at the beginning of the thread. I have never, ever stated that I would never use CTEs. You're either lying to talk yourself out of this conversation or did not understand my statment.


>>Yes one CTE is to solve a recursive problem. And no there are no repeatable subqueries in the example.
>>If you don't want to even listen to the arguments and dismiss them without even looking into the example, please stop wasting both our time and say so.


>I looked into the example, but unlike mine you didn't provide a sample that just would work if I tried to run in say SSMS. As I said, the only option you left me there was to read that, and when it reached to first subquery turned out to be ugly spaghetti code, I stopped deciphering further. If there were no repetitions, what were your excuse to use a CTE I wonder.

oh please…., I could not execute your examples as well. One was using an unspecified "VeryExpensiveFunction()" and the other was in Postgress SQL which will not run in SSMS either.


>>Sorry, I can't understand your position on this. Perhaps my brains are wired differently than yours, but the subqueries are a million times better to read than splitting !! them up in several CTEs. You then miss the plot. Its like having a dozen legoblocks (CTEs) on the flow and see a car. Nope, not working for me.
>
>Yes my brain is trained to read from top to bottom, left to right, you are right. OTOH no idea what you mean by "the subqueries are a million times better to read than splitting", what split, how you reasoned they are better be it 2x or 1 million. Lego sample is simply totally irrelevant, just saying something as if it was true with using meaningless analogy.

Not it is. The CTEs can be considered as legoblocks as they are the building blocks of the query. The SQL statment can be considered the manual how to build the lego block to a full functioning SQL statement.

And this is Exactly what the RDBMS does (for MS SQL, ORACLE, POSTGRESS and all others): Building the SQL statements from the lego blocks.

To take this analogy further, when looking at the lego blocks individually, you might not have a clue where they are going to be used for. It is only when you have put the lego blocks together you can see how it works. You might see that the same (type of) lego block was used for for two different purposes. One as a wheel axis and another as a bumper. (A CTE used twice, but for different subqueries)

I can't believe I have to explain this to an obvious experienced programmer like you.
* Don't you agree that implementing a CTE is a splitting the query into CTE declarations and actual execution ???
* Don't you agree that the definition and usage could be many pages apart, with loads of other non-related code in between?

>Cheap statements, me :) Ok let's be it. Keep the lego pieces and never ever use CTEs.

Look up "Overused" in the dictionary. You're playing a game of denial here. I'd expect better from you. Very dissapointing.