SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Syntax issues with nested CTE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kcarbone1970
Starting Member

USA
31 Posts

Posted - 09/30/2013 :  12:25:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 09/30/2013 :  12:36:02  Show Profile  Reply with Quote
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?
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 09/30/2013 :  12:52:00  Show Profile  Reply with Quote
Trying to UNION the results of two queries one of which happens to be a cte...
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 09/30/2013 :  12:58:25  Show Profile  Reply with Quote
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;
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 09/30/2013 :  13:00:23  Show Profile  Reply with Quote
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
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 09/30/2013 :  13:02:48  Show Profile  Reply with Quote
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;
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 09/30/2013 :  13:04:31  Show Profile  Reply with Quote
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

Edited by - kcarbone1970 on 09/30/2013 13:14:31
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 09/30/2013 :  14:18:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

kcarbone1970
Starting Member

USA
31 Posts

Posted - 09/30/2013 :  15:06:44  Show Profile  Reply with Quote
Thanks Again.....You are awesome!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000