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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kcarbone1970
Yak Posting Veteran

USA
52 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

3873 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
Yak Posting Veteran

USA
52 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

3873 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
Yak Posting Veteran

USA
52 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
Yak Posting Veteran

USA
52 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
Yak Posting Veteran

USA
52 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
30421 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
Yak Posting Veteran

USA
52 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  
 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.12 seconds. Powered By: Snitz Forums 2000