November 7, 2013 at 12:57 am
I am trying to use a cte to compile this query and I am getting this error 'cteSource' has fewer columns than were specified in the column list.
DECLARE @SC INT = 30;
DECLARE @GR INT = 10;
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 derived
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]
November 7, 2013 at 1:27 am
On the line that defines the CTE:
WITH cteSource ( NAME, ID, GR, SC,
You have a column called NAME, but the select that immediately follows starts with a value for ID. I think you just need to add the name column to the list of columns defined for the CTE.
Or remove the named columns entirely and rename each column as needed in the Select.
Mike John
November 7, 2013 at 1:36 am
'NAME' is in the SELECT but it is the fourth column and has been described as the first column in the column names list.
Remove the column names so it read as:WITH cteSource AS ( SELECT then look at the columns produced. This should help you see what columns it it showing and in what sequence.
November 7, 2013 at 8:29 am
This code is a bit confusing to follow, but as a previous poster indicated your statement
WITH cteSource ( NAME, ID, GR, SC, Ethnicity, Gate, SpecialED, LF,...
has to match the number of columns in your AS (SELECT statement, which it doesn't. One of the things I do to debug a CTE is to break out the code producing the CTE, run it to insure the expected results is appearing.
Also personally I'm not a big fan of producing result sets from sub-selects. I'd refine the code so you are producing multiple CTE resultsets then utilizing them by joining them back into a final query. One of my personal goals is to make code not so complicated so it can be easily adapted to any future changes. It also helps in debugging a process in the event unexpected results comes up. Just saying....
Good luck.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply