Viewing 15 posts - 16 through 30 (of 106 total)
I will try them and let you know. I like more the TVF than the function in this case. Right now, view is working fine but I'm facing a new...
February 17, 2020 at 2:08 pm
No, it's not, because it's not part of a recursive table expression. Somehow it seems that SQL solves that query before and then you can use it.
By the way, this...
February 17, 2020 at 1:49 pm
No, this won't work. As I said before, you can't have aggregate functions in the recursive part of a recursive CTE.
February 17, 2020 at 1:11 pm
Yes, maybe I didn't understand you completely. I thought you were suggesting to have that outside CTE but as LEFT JOIN in the SELECT FROM CTE.
So, what do you suggest?...
February 17, 2020 at 11:43 am
I need it, otherwise I'm getting an aritmethic overflow error, even defined as decimal(38,6)
February 17, 2020 at 11:30 am
Sure, here is:
USE [DBMCS_BK]
GO
/****** Object: View [dbo].[vRecipe] Script Date: 2/16/2020 9:04:31 PM ******/
DROP VIEW IF EXISTS [dbo].[vRecipe]
GO
/****** Object: View [dbo].[vRecipe] ...
February 17, 2020 at 11:03 am
Here is the new plan with first option (UNION ALL SELECT NULL, NULL). What's the purpose of the UNION ALL in this case?
February 17, 2020 at 10:22 am
Current view is using your Aggreg_NR CTE. Beside that, I've changed the indexes, that helped a little. I don't like the index spool in current execution plan.
I...
February 17, 2020 at 9:43 am
I've replaced a couple of indexes, including some fields in order to avoid key lookups. Now performance looks a bit better, as you can see here:
Still wondering...
February 17, 2020 at 9:30 am
-- option 2
or if the aggreg_nr still does not work then replace all the outer applies with
outer apply (select sum(amt) over (order by nr) from dbo.dbdopsk dop where dop.nr =...
February 17, 2020 at 9:12 am
Performance is slighty better, however is still slower that UNION ALL version.
Look at SQL Sentry Explorer.
February 17, 2020 at 9:09 am
Trying first option with OUTER APPLY. LEFT JOIN is not allowed in the recursive part of a recursive common table expresion.
February 17, 2020 at 9:02 am
You cannot use GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expresion. In theory that query looks better, but I...
February 17, 2020 at 8:25 am
You cannot use SELECT TOP in a CTE, that's why.
February 17, 2020 at 7:27 am
I know I can't rely on those percentages, I used STATISTICS and I was confident with that results.
Here are the views:
CREATE VIEW [dbo].[RECIPE] AS
SELECT 0 as OPNR,...
February 16, 2020 at 8:05 pm
Viewing 15 posts - 16 through 30 (of 106 total)
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy