August 29, 2013 at 11:40 am
Hi All, i have this script:
;WITH Estimated AS (
SELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1,
Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX))
,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' +
RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]'
,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos'
FROM testprojects tproy (NOLOCK)
INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_id
INNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_id
INNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_id
INNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.id
WHERE tproy.id=2
AND cdv.value NOT like '%[A-Z]%'
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
UNION ALL
SELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1,
Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX))
,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' +
RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]'
,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos'
FROM testprojects tproy (NOLOCK)
INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_id
INNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_id
INNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_id
INNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.id
INNER JOIN Estimated e ON nh.parent_id=e.id
WHERE tproy.id=2
AND cdv.value NOT like '%[A-Z]%'
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
)
SELECT id,parent_id,name = SPACE((nodes_level -1)*4) + name,nodes_level,jerarquia FROM Estimated
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
ORDER BY Jerarquia;
give me this error:
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'Estimated'.
Thanks
____________________________________________________________________________
Rafo*
August 29, 2013 at 11:50 am
The error you are getting is true: GROUP BY/HAVING,etc are not allowed in the recursive part of a CTE. It does not look like you are trying to do a recursive CTE but your UNION ALL statement is causing SQL to interpret your query that way....
You have two options:
1. You may be able (hard to tell without any DDL [hint]) to get away with using UNION instead (assuming you don't need duplicates) and that should solve your problem.
2. Insead of a CTE use a subquery... Something like:
SELECT id,parent_id,name = SPACE((nodes_level -1)*4) + name,nodes_level,jerarquia
FROM
(
SELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1,
Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX))
,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' +
RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]'
,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos'
FROM testprojects tproy (NOLOCK)
INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_id
INNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_id
INNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_id
INNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.id
WHERE tproy.id=2
AND cdv.value NOT like '%[A-Z]%'
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
UNION ALL
SELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1,
Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX))
,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' +
RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]'
,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos'
FROM testprojects tproy (NOLOCK)
INNER JOIN testplans tplan (NOLOCK) ON tproy.id=tplan.testproject_id
INNER JOIN testplan_tcversions tcver (NOLOCK) ON tplan.id=tcver.testplan_id
INNER JOIN cfield_design_values cdv (NOLOCK) ON tcver.tcversion_id=cdv.node_id
INNER JOIN nodes_hierarchy nh (NOLOCK) ON tplan.id=nh.id
INNER JOIN Estimated e ON nh.parent_id=e.id
WHERE tproy.id=2
AND cdv.value NOT like '%[A-Z]%'
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
) AS Estimated
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
ORDER BY Jerarquia;
Edit: Typo
-- Itzik Ben-Gan 2001
August 29, 2013 at 11:57 am
Also,
I'd get rid of those NOLOCK table hints. NOLOCK (READ UNCOMMITTED) is for when you don't care if you always get the right answer. Here's a good article about that:
SQL Server NOLOCK Hint & other poor ideas.
-- Itzik Ben-Gan 2001
August 29, 2013 at 12:06 pm
Actually the OP is doing a recursive cte. The cte is named Estimate and the second half joins to it.
You will probably have to use your cte to return the raw data and then do your aggregates.
Also, get rid of those NOLOCK hints. Do you know all the nasty stuff that goes with those hints? Missing and/or duplicate data? Impossible to recreate bugs.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2013 at 2:16 pm
xRafo (8/29/2013)
ok I get it on the NOLOCK. Thanks.@alan.B
your query give me this error:
Invalid object name 'Estimated'. 🙁
That is because he missed that you had a cte named estimated. The query he posted is trying to do a join to it but it doesn't exist.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2013 at 2:29 pm
I am having a hard time trying to figure out what you are trying to do here. Your original query has a recursive cte but you don't reference the original values in the recursive part. You seem to have multiple levels of aggregation here too which is very confusing.
Something like this might be getting closer. However in your original query you referenced nodes_level in the select but it isn't listed in the cte. I am not sure what that column is or where it comes from.
;WITH Estimates AS
(
SELECT nh.id,nh.parent_id,tproy.prefix,nh.name AS 'PLAN',Levelnodes=1,
Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX))
,CONVERT(VARCHAR,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))/60)) + ':' +
RIGHT('0' + CAST (SUM(CONVERT(decimal(8,4),cdv.value))%60 AS INT),2) AS '[HH:mm]'
,CONVERT(INT,SUM(CONVERT(decimal(8,4),cdv.value))) AS 'TotalMinutos'
FROM testprojects tproy
INNER JOIN testplans tplan ON tproy.id=tplan.testproject_id
INNER JOIN testplan_tcversions tcver ON tplan.id=tcver.testplan_id
INNER JOIN cfield_design_values cdv ON tcver.tcversion_id=cdv.node_id
INNER JOIN nodes_hierarchy nh ON tplan.id=nh.id
WHERE tproy.id=2
AND cdv.value NOT like '%[A-Z]%'
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
)
,
Estimated as
(
select id, parent_id, prefix, [PLAN], Levelnodes, Jerarquia, [HH:mm], TotalMinutos
from Estimates
union all
select id, parent_id, prefix, [PLAN], Levelnodes + 1, Jerarquia, [HH:mm], TotalMinutos
from Estimates e
join Estimated estd on e.id = estd.parent_id
)
SELECT id, parent_id, name = SPACE((nodes_level -1)*4) + name, nodes_level, jerarquia
FROM Estimated
GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
ORDER BY Jerarquia;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2013 at 2:48 pm
Thanks.
I modified the script a bit and doesn't generate errors:-), but isn't listing the desired result .. please see document attached (img_1/DataExample.txt). If you can see the nodes_level with hierarchy..
Please img_2 to see the result of your script
thanks for your help, any questions please let me know.
Thank you again!
____________________________________________________________________________
Rafo*
August 29, 2013 at 2:59 pm
xRafo (8/29/2013)
Thanks.I modified the script a bit and doesn't generate errors:-), but isn't listing the desired result .. please see document attached (img_1/DataExample.txt). If you can see the nodes_level with hierarchy..
Please img_2 to see the result of your script
thanks for your help, any questions please let me know.
Thank you again!
So what is wrong with the query you posted?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2013 at 3:22 pm
Retry deleted.
____________________________________________________________________________
Rafo*
August 29, 2013 at 3:49 pm
Hey,
i was editing the script, now i get this error:
script:
;WITH Estimates AS
(SELECT nh.id,nh.parent_id,nh.name AS 'PLAN',Levelnodes=1,
Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX))
FROM testprojects tproy
INNER JOIN testplans tplan ON tproy.id=tplan.testproject_id
INNER JOIN testplan_tcversions tcver ON tplan.id=tcver.testplan_id
INNER JOIN cfield_design_values cdv ON tcver.tcversion_id=cdv.node_id
INNER JOIN nodes_hierarchy nh ON nh.id=tplan.id
WHERE tproy.id=2
AND cdv.value NOT like '%[A-Z]%'
GROUP BY nh.name,nh.id,nh.parent_id
UNION ALL
SELECT nh.id,nh.parent_id,nh.name AS 'PLAN',Levelnodes=Levelnodes + 1,
Jerarquia = CAST('\'+CAST(nh.name AS VARCHAR(200)) AS VARCHAR(MAX))
FROM testprojects tproy
INNER JOIN testplans tplan ON tproy.id=tplan.testproject_id
INNER JOIN testplan_tcversions tcver ON tplan.id=tcver.testplan_id
INNER JOIN cfield_design_values cdv ON tcver.tcversion_id=cdv.node_id
INNER JOIN nodes_hierarchy nh ON nh.id=tplan.id
INNER JOIN Estimates e ON nh.parent_id=e.ID
WHERE cdv.value NOT like '%[A-Z]%'
GROUP BY nh.name,nh.id,nh.parent_id
),
Estimated as
(
/*select id, parent_id, prefix, [PLAN], Levelnodes, Jerarquia, [Hours], TotalMinutos
from Estimates
union all
select e.id, e.parent_id, e.prefix, e.[PLAN], e.Levelnodes + 1, e.Jerarquia, e.[Hours],
e.TotalMinutos
from Estimates e
join Estimated estd on e.id = estd.parent_id*/
select id, parent_id, prefix, [PLAN], Levelnodes, Jerarquia
from Estimates
union all
select e.id, e.parent_id, e.prefix, e.[PLAN], e.Levelnodes , e.Jerarquia
from Estimates e
join Estimated estd on estd.parent_id=e.id
)
SELECT *
FROM Estimated
--GROUP BY tproy.prefix,nh.name,nh.id,nh.parent_id
ORDER BY Jerarquia;
please see images attached
____________________________________________________________________________
Rafo*
August 30, 2013 at 7:19 am
That's because you added aggregates back into your recursive cte.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply