Tag Info

New answers tagged

5

Just a word about nested CTEs, they get evaluated each time they are referenced. To put it another way, they do not behave like temp tables, the data is not cached in some special way. So for a CTE referenced multiple times as yours are ( ie once in the UNION, once in the RunningTotal subquery ), there can be a performance penalty. I set up a simple rig ...


3

You have two problems. First you need commas between the CTEs. Second you can only have one ORDER BY for a group of UNIONed statements. Just use one ORDER BY at the very end. WITH [ProgressInvoicesPeriodCost] AS ( SELECT ProgressInvoices.ProjectId , ProgressInvoices.PeriodId , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS ...


5

I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore. The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version. (Note that all of ...


0

On Oct 24, 2011, I wrote some Stored Functions to perform Ancestor and Descendant traversals (Find highest level of a hierarchical field: with vs without CTEs ). From my post, you would use GetAncestry. From your data, the code in GetAncestry will traverse until it sees -1 as a parent id. No changes in the algorithm are needed except to change parent_id to ...


1

I figured it out. (Apologies to those of you who tried to help me. There was no way you could have figured this out.) After a lot of experimentation, I noticed that dynamic queries (Connection.createStatement()) were returning results but parameterized queries (Connection.prepareStatement()) were returning an empty set. You had no way of knowing this ...



Top 50 recent answers are included