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)
 help with cte
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kcarbone1970
Starting Member

USA
31 Posts

Posted - 11/07/2013 :  02:20:31  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/07/2013 :  04:16:47  Show Profile  Reply with Quote
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
  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.92 seconds. Powered By: Snitz Forums 2000