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)
 help with cte

Author  Topic 

kcarbone1970
Yak Posting Veteran

52 Posts

Posted - 2013-11-07 : 02:20:31
I am getting this error 'cteSource' has fewer columns than were specified in the column list. Any help greatly appreciated!



DECLARE @GR INT = 10;
DECLARE @SC INT = 30;

WITH cteSource ( NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF, [A-G C- or Better], Affliation, Tardies, Referrals, Absences, [A-G Schedule] )
AS ( SELECT s.ID ,
s.GR ,
s.SC ,
s.LN + ', ' + s.FN AS NAME ,
( CASE WHEN s.RC1 = 700
AND ETH = 'Y' THEN 'Hispanic'
WHEN s.RC1 = 600
AND ETH = 'N' THEN 'African American'
ELSE 'Other'
END ) AS Ethnicity ,
( CASE WHEN s.U7 = 'G' THEN 'Yes'
ELSE 'No'
END ) AS Gate ,
( CASE WHEN s.U9 IN ( 'R', 'D', 'S' ) THEN 'Yes'
ELSE 'No'
END ) AS SpecialEd ,
( CASE WHEN s.LF = '1' THEN 'English Only'
WHEN s.LF = '2'
THEN 'Initially Fluent English Proficient'
WHEN s.LF = '3' THEN 'English Learner'
WHEN s.LF = '4'
THEN 'Redesignated Fluent English Proficient'
WHEN s.LF = '5' THEN 'TBD'
ELSE '?'
END ) AS LF ,
( SELECT CASE WHEN HISTORY >= 0
AND ELA >= 1
AND MATH >= 1
AND SCIENCE >= 1
AND FL >= 1
AND VA >= 0
AND Prep >= 0 THEN 'Yes'
ELSE 'No'
END AS [A-G C- or Better]
FROM ( SELECT SUM(HISTORY) AS HISTORY ,
SUM(ELA) AS ELA ,
SUM(MATH) AS MATH ,
SUM(SCIENCE) AS SCIENCE ,
SUM(FL) AS FL ,
SUM(VA) AS VA ,
SUM(Prep) AS Prep ,
COUNT(*) AS Items
FROM ( SELECT SUM(CASE
WHEN c.U1 = 'A'
THEN 1
ELSE 0
END) AS HISTORY ,
SUM(CASE
WHEN c.U1 = 'B'
THEN 1
ELSE 0
END) AS ELA ,
SUM(CASE
WHEN c.U1 = 'C'
THEN 1
ELSE 0
END) AS MATH ,
SUM(CASE
WHEN c.U1 = 'D'
THEN 1
ELSE 0
END) AS SCIENCE ,
SUM(CASE
WHEN c.U1 = 'E'
THEN 1
ELSE 0
END) AS FL ,
SUM(CASE
WHEN c.U1 = 'F'
THEN 1
ELSE 0
END) AS VA ,
SUM(CASE
WHEN c.U1 = 'G'
THEN 1
ELSE 0
END) AS Prep
FROM CRS AS c
INNER JOIN HIS AS h ON h.CN = c.CN
AND ( h.MK NOT IN (
'F', 'F+', 'F-',
'D', 'D-', 'D+',
'NM', 'NC' ) )
INNER JOIN STU AS s ON s.ID = h.PID
AND s.GR = @GR
AND s.SC = @SC
AND s.TG NOT IN (
'N', '*', 'I' )
WHERE c.U1 IN ( 'A', 'B',
'C', 'D', 'E',
'F', 'G' )
UNION ALL
SELECT SUM(CASE
WHEN c.U1 = 'A'
THEN 1
ELSE 0
END) AS HISTORY ,
SUM(CASE
WHEN c.U1 = 'B'
THEN 1
ELSE 0
END) AS ELA ,
SUM(CASE
WHEN c.U1 = 'C'
THEN 1
ELSE 0
END) AS MATH ,
SUM(CASE
WHEN c.U1 = 'D'
THEN 1
ELSE 0
END) AS SCIENCE ,
SUM(CASE
WHEN c.U1 = 'E'
THEN 1
ELSE 0
END) AS FL ,
SUM(CASE
WHEN c.U1 = 'F'
THEN 1
ELSE 0
END) AS VA ,
SUM(CASE
WHEN c.U1 = 'G'
THEN 1
ELSE 0
END) AS Prep
FROM dbo.CRS AS c
INNER JOIN GRD AS g ON g.CN = c.CN
AND ( g.M1 NOT IN (
'F', 'F+', 'F-',
'D', 'D-', 'D+',
'NM', 'NC' ) )
INNER JOIN STU AS s ON s.SN = g.SN
AND s.GR = @GR
AND s.SC = @SC
AND s.TG NOT IN (
'N', '*', 'I' )
WHERE c.U1 IN ( 'A', 'B',
'C', 'D', 'E',
'F', 'G' )
) AS d
) AS d
UNION ALL
SELECT COALESCE(MIN(CASE WHEN a.SD IS NOT NULL
AND a.ED IS NULL
AND a.CD NOT BETWEEN 600 AND 699
THEN 'Yes'
ELSE 'No'
END), 'No') AS Affliation
FROM STU AS s
LEFT JOIN ACT AS a ON a.PID = s.ID
WHERE s.TG NOT IN ( 'N', '*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
UNION ALL
SELECT CASE WHEN COALESCE([Total Tardies], 0) <= 6
THEN 'Yes'
ELSE 'No'
END AS Tardies
FROM ( SELECT SUM(CASE WHEN a.[A1] IN ( 'T',
'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A2] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A3] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A4] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A5] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A6] IN (
'T', 'Z', 'B' )
THEN 1
ELSE 0
END) AS [Total Tardies]
FROM ATT AS a
INNER JOIN STU AS s ON s.SN = a.SN
AND s.TG NOT IN (
'N', '*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
) AS d
UNION ALL
SELECT CASE WHEN COUNT(a.PID) >= 2
AND SUM(CASE WHEN a.DS = 'SUS'
THEN 1
ELSE 0
END) >= 1 THEN 'No'
ELSE 'Yes'
END AS Referrals
FROM STU AS s
LEFT JOIN dbo.ADS AS a ON a.PID = s.ID
WHERE s.TG NOT IN ( 'N', '*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
UNION ALL
SELECT CASE WHEN [Total Absenecs] <= 12
THEN 'Yes'
ELSE 'No'
END AS Absences
FROM ( SELECT SUM(CASE WHEN a.[A1] IN ( 'A',
'E', 'I', 'K',
'L', 'S', 'U',
'X' ) THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A2] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A3] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A4] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A5] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END)
+ SUM(CASE WHEN a.[A6] IN (
'A', 'E', 'I',
'K', 'L', 'S',
'U', 'X' )
THEN 1
ELSE 0
END) AS [Total Absenecs]
FROM ATT AS a
INNER JOIN dbo.STU AS s ON s.SN = a.SN
AND s.TG NOT IN (
'N', '*', 'I' )
AND s.SC = @SC
AND s.GR = @GR
) AS d
UNION ALL
SELECT CASE WHEN A >= 0
AND B >= 1
AND C >= 1
AND D >= 1
AND E >= 1
AND F >= 0
AND G >= 0 THEN 'Yes'
ELSE 'No'
END AS [A-G Schedule]
FROM ( SELECT SUM(A) AS A ,
SUM(B) AS B ,
SUM(C) AS C ,
SUM(D) AS D ,
SUM(E) AS E ,
SUM(F) AS F ,
SUM(G) AS G
FROM ( SELECT SUM(CASE
WHEN r.U1 = 'A'
THEN 1
ELSE 0
END) AS A ,
SUM(CASE
WHEN r.U1 = 'B'
THEN 1
ELSE 0
END) AS B ,
SUM(CASE
WHEN r.U1 = 'C'
THEN 1
ELSE 0
END) AS C ,
SUM(CASE
WHEN r.U1 = 'D'
THEN 1
ELSE 0
END) AS D ,
SUM(CASE
WHEN r.U1 = 'E'
THEN 1
ELSE 0
END) AS E ,
SUM(CASE
WHEN r.U1 = 'F'
THEN 1
ELSE 0
END) AS F ,
SUM(CASE
WHEN r.U1 = 'G'
THEN 1
ELSE 0
END) AS G
FROM STU AS t
LEFT JOIN dbo.SSS
AS s ON s.SN = t.SN
LEFT JOIN dbo.CRS
AS r ON r.CN = s.CN
AND r.U1 IN (
'A', 'B', 'C',
'D', 'E', 'F',
'G' )
WHERE t.TG NOT IN ( 'N',
'*', 'I' )
AND t.GR = @GR
AND t.SC = @SC
UNION ALL
SELECT SUM(CASE
WHEN r.U1 = 'A'
THEN 1
ELSE 0
END) AS A ,
SUM(CASE
WHEN r.U1 = 'B'
THEN 1
ELSE 0
END) AS B ,
SUM(CASE
WHEN r.U1 = 'C'
THEN 1
ELSE 0
END) AS C ,
SUM(CASE
WHEN r.U1 = 'D'
THEN 1
ELSE 0
END) AS D ,
SUM(CASE
WHEN r.U1 = 'E'
THEN 1
ELSE 0
END) AS E ,
SUM(CASE
WHEN r.U1 = 'F'
THEN 1
ELSE 0
END) AS F ,
SUM(CASE
WHEN r.U1 = 'G'
THEN 1
ELSE 0
END) AS G
FROM STU AS s
LEFT JOIN dbo.HIS
AS i ON i.PID = s.ID
LEFT JOIN dbo.CRS
AS r ON r.CN = i.CN
AND r.U1 IN (
'A', 'B', 'C',
'D', 'E', 'F',
'G' )
WHERE s.TG NOT IN ( 'N',
'*', 'I' )
AND s.GR = @GR
AND s.SC = @SC
) AS d
) AS d
) AS dervied
FROM STU AS s
)
SELECT *
FROM cteSource
GROUP BY NAME ,
ID ,
GR ,
SC ,
Ethnicity ,
Gate ,
SpecialED ,
LF ,
[A-G C- or Better] ,
Affliation ,
Tardies ,
Referrals ,
Absences ,
[A-G Schedule]



Cartesian Yak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-07 : 04:16:47
Reason is you dont have all the columns coming in all queries with UNION ALL. I think what you need is join functionslity instead which will merge all the columns onto same resultset so that it matches CTE definition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -