Syntax issues with nested CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Syntax issues with nested CTE

Author

Topic

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-30 : 12:25:19

I need to create a query that is comprised of subqueries, I am using a CTE, but one of the subqueries also is using a CTE...Can you nest CTE like this??



WITH CTE_Results AS
(
SELECT
    CASE WHEN HISTORY >= 2 AND
              ELA >= 4 AND
              MATH >= 4 AND
              SCIENCE >= 3 AND
              FL >= 3 AND
              VA >= 1 AND
              Prep >= 0
         THEN 'Yes'
         ELSE 'No' END AS [On Target?]    
FROM (
    SELECT
        
        COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS HISTORY,
        COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS ELA,
        COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS MATH,
        COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS SCIENCE,
        COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS FL,
        COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS VA,
        COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS Prep
    FROM dbo.CRS INNER JOIN
         dbo.HIS ON CRS.CN = HIS.CN INNER JOIN
         dbo.STU ON HIS.PID = STU.ID
    WHERE
        STU.ID = 4068968
) AS derived




UNION ALL



WITH cteSource(CN, U1)
AS (
	SELECT		r.CN,
			r.U1
	FROM		dbo.SSS AS s 
	INNER JOIN	dbo.STU AS t ON t.SN = s.SN 
	INNER JOIN	dbo.CRS AS r ON r.CN = s.CN
	WHERE		t.ID = 4068968

	UNION ALL

	SELECT		r.CN,
			r.U1
	FROM		dbo.HIS AS i 
	INNER JOIN	dbo.CRS AS r ON r.CN = i.CN
	WHERE		i.PID = 4068968
)
SELECT	
	CASE
		WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes' 
		ELSE 'No' 
	END AS [On Target?]
FROM	cteSource AS s
PIVOT	(
		COUNT(s.CN)
		FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G])
	) AS p;



SELECT CONVERT(VARCHAR(5),SUM(CASE WHEN [On Target?] = 'Yes' THEN 1 ELSE 0 END)) + '/2'
FROM CTE_Results

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 12:36:02

The syntax definitely is not correct - I don't have to tell you that :) But, it is not clear to me what you are trying to do - are you trying to union the results of two cte's, or are you trying to use a recursive CTE?

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-30 : 12:52:00

Trying to UNION the results of two queries one of which happens to be a cte...

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-30 : 12:58:25

Perhaps like shown below? Some of the CTE's may not be required - or they could perhaps be split into multiple ctes (as in the case of cteSource; Also, my logic may not be quite right. But, what you have to do is to create all the CTE's one after the other in the manner that I have shown below, and then do your union all in the final select.

WITH cteSource(CN, U1)
AS (
	SELECT		r.CN,
			r.U1
	FROM		dbo.SSS AS s 
	INNER JOIN	dbo.STU AS t ON t.SN = s.SN 
	INNER JOIN	dbo.CRS AS r ON r.CN = s.CN
	WHERE		t.ID = 4068968

	UNION ALL

	SELECT		r.CN,
			r.U1
	FROM		dbo.HIS AS i 
	INNER JOIN	dbo.CRS AS r ON r.CN = i.CN
	WHERE		i.PID = 4068968
),
CTE_Intermediate AS
(
	SELECT	
		CASE
			WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes' 
			ELSE 'No' 
		END AS [On Target?]
	FROM	cteSource AS s
	PIVOT	(
			COUNT(s.CN)
			FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G])
		) AS p
),
CTE_Results AS
(
	SELECT
		CASE WHEN HISTORY >= 2 AND
				  ELA >= 4 AND
				  MATH >= 4 AND
				  SCIENCE >= 3 AND
				  FL >= 3 AND
				  VA >= 1 AND
				  Prep >= 0
			 THEN 'Yes'
			 ELSE 'No' END AS [On Target?]    
	FROM (
		SELECT
	        
			COUNT(CASE WHEN CRS.U1 = 'A' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS HISTORY,
			COUNT(CASE WHEN CRS.U1 = 'B' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS ELA,
			COUNT(CASE WHEN CRS.U1 = 'C' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS MATH,
			COUNT(CASE WHEN CRS.U1 = 'D' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS SCIENCE,
			COUNT(CASE WHEN CRS.U1 = 'E' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS FL,
			COUNT(CASE WHEN CRS.U1 = 'F' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS VA,
			COUNT(CASE WHEN CRS.U1 = 'G' AND (HIS.MK NOT LIKE '%FD%') THEN HIS.CN END) AS Prep
		FROM dbo.CRS INNER JOIN
			 dbo.HIS ON CRS.CN = HIS.CN INNER JOIN
			 dbo.STU ON HIS.PID = STU.ID
		WHERE
			STU.ID = 4068968
	) AS derived
)
SELECT * FROM CTE_Intermediate
UNION ALL
SELECT * FROM CTE_Results;

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-30 : 13:00:23

I have several queries that result in a yes/no outcome that now we want to see how many yes/no's a particular individual has, then group them accordingly. Actually they all work within the CTE but there is one query that is also a CTE that needs to be included and I cant get past the syntax

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-30 : 13:02:48

actually this query is one

WITH cteSource(CN, U1)
AS (
SELECT r.CN,
r.U1
FROM dbo.SSS AS s
INNER JOIN dbo.STU AS t ON t.SN = s.SN
INNER JOIN dbo.CRS AS r ON r.CN = s.CN
WHERE t.ID = 4068968

UNION ALL

SELECT r.CN,
r.U1
FROM dbo.HIS AS i
INNER JOIN dbo.CRS AS r ON r.CN = i.CN
WHERE i.PID = 4068968
)
SELECT
CASE
WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes'
ELSE 'No'
END AS [On Target?]
FROM cteSource AS s
PIVOT (
COUNT(s.CN)
FOR s.U1 IN ([A], [B], [C], [D], [E], [F], [G])
) AS p;

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-30 : 13:04:31

thanks for helping....Im trying to count the number of "yes's" that appear in each subquery....I need the result to end in a fraction 2/2 or 1/2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-30 : 14:18:52

[code]DECLARE @ID INT = 4068968;

WITH cteSource([On Target?])
AS (
SELECT CASE
WHEN HISTORY >= 2 AND ELA >= 4 AND MATH >= 4 AND SCIENCE >= 3 AND FL >= 3 AND VA >= 1 THEN 'Yes'
ELSE 'No'
END AS [On Target?]
FROM (
SELECT SUM(CASE WHEN r.U1 = 'A' THEN 1 ELSE 0 END) AS HISTORY,
SUM(CASE WHEN r.U1 = 'B' THEN 1 ELSE 0 END) AS ELA,
SUM(CASE WHEN r.U1 = 'C' THEN 1 ELSE 0 END) AS MATH,
SUM(CASE WHEN r.U1 = 'D' THEN 1 ELSE 0 END) AS SCIENCE,
SUM(CASE WHEN r.U1 = 'E' THEN 1 ELSE 0 END) AS FL,
SUM(CASE WHEN r.U1 = 'F' THEN 1 ELSE 0 END) AS VA
FROM dbo.CRS AS r
INNER JOIN dbo.HIS AS i ON i.CN = r.CN
AND i.MK NOT LIKE '%FD%'
INNER JOIN dbo.STU AS t ON t.ID = i.PID
AND t.ID = @ID
WHERE r.U1 LIKE '[A-F]'
) AS d

UNION ALL

SELECT CASE
WHEN p.A >= 6 AND p.B >= 6 AND p.C >= 6 AND p.D >= 6 AND p.E >= 6 AND p.F >= 6 AND p.G >= 6 THEN 'Yes'
ELSE 'No'
END AS [On Target?]
FROM (
SELECT r.CN,
r.U1
FROM dbo.SSS AS s
INNER JOIN dbo.STU AS t ON t.SN = s.SN
INNER JOIN dbo.CRS AS r ON r.CN = s.CN
WHERE t.ID = @ID

UNION ALL

SELECT r.CN,
r.U1
FROM dbo.HIS AS i
INNER JOIN dbo.CRS AS r ON r.CN = i.CN
WHERE i.PID = @ID
) AS d
PIVOT (
COUNT(d.CN)
FOR d.U1 IN ([A], [B], [C], [D], [E], [F], [G])
) AS p
)
SELECT CAST(SUM(CASE WHEN [On Target?] = 'Yes' THEN 1 ELSE 0 END) AS VARCHAR(12)) + '/' + CAST(COUNT(*) AS VARCHAR(12))
FROM cteSource;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-09-30 : 15:06:44

Thanks Again.....You are awesome!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources