Error that I am not seeing - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Error that I am not seeing

Author

Topic

jpost
Starting Member

43 Posts

Posted - 2012-09-06 : 13:03:31

I am using the following script to pull information on my students. For some reason after my sub-select the s is underlined and I get an error stating No column name was specified for column 3 of 's'. It works in other queries that I am using the same way but is giving me an issue here. Suggestions? It is the s before the rn=1

select * From
(select distinct
cast(s.studentNumber as char(9))as 'Sutdent Number',
cast (sc.number as char (6)) as 'Building Code',
case sc.number
when 5101 then '029876'
when 5102 then '145086'
when 5111 then '112581'
when 5112 then '145078'
when 5121 then '138966'
when 5122 then '138958'
when 5123 then '011369'
when 5131 then '086579'
when 5135 then '142638'
when 5132 then '029868'
when 5133 then '065433'
when 5137 then '011368'
when 5136 then '011370'
when 5134 then '123307'
end,
cast (s.lastName + ', ' + s.firstName as CHAR (36)) as 'Name',
cast (s.gender as char (1)) as'Gender',
/* STUFF(ma.addressLine2,CHARINDEX(',',ma.addressLine2),LEN(ma.addressLine2),'') City,
LEFT(LTRIM(STUFF(ma.addressLine2,1,CHARINDEX(',',ma.addressLine2),'')),2) State,
RIGHT(RTRIM(ma.addressLine2),5) Zip,*/
a.number + ' ' + a.street + ' ' + a.tag as 'Address',
a.city,
a.state,
a.zip,
cast (hh.phone as CHAR (14)) as 'Phone',
convert(varchar,s.birthdate,111)AS 'birthDate',
cast (s.grade as CHAR(6)) as 'Grade',
se.teacherdisplay as 'Homeroom',
ROW_NUMBER() OVER (PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN
From student s
INNER JOIN school sc on sc.schoolID = s.schoolID
INNER JOIN HouseholdMember hm ON hm.personID = s.personID
INNER JOIN Household hh ON hh.householdID = hm.householdID
INNER JOIN HouseholdLocation hl ON hl.householdID = hh.householdID
INNER JOIN Address a ON a.addressID = hl.addressID
JOIN Roster r ON r.personID = s.personID
JOIN Section se ON se.sectionID = r.sectionID
JOIN SectionPlacement sp ON sp.sectionID = se.sectionID
JOIN Period p ON p.periodID = sp.periodID
JOIN Course c ON c.courseID = se.courseID
JOIN Calendar ca ON ca.calendarID = c.calendarID AND ca.calendarID = s.calendarID and sc.schoolID = ca.schoolID
JOIN SchoolYear sy ON sy.endYear = ca.endYear
where s.calendarID in (8,12,16,20,24,28,32,36,40,44,48,52,56,60) and s.startYear = '2012'
and p.name in ('Homeroom','1')or c.homeroom = '1')s
where RN = 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-06 : 13:14:07

Add an alias to the third column in the subquery

                  CAST(s.studentNumber AS CHAR(9)) AS 'Sutdent Number',
                  CAST(sc.number AS CHAR(6)) AS 'Building Code',
                  CASE sc.number
                       WHEN 5101 THEN '029876'
                       WHEN 5102 THEN '145086'
                       WHEN 5111 THEN '112581'
                       WHEN 5112 THEN '145078'
                       WHEN 5121 THEN '138966'
                       WHEN 5122 THEN '138958'
                       WHEN 5123 THEN '011369'
                       WHEN 5131 THEN '086579'
                       WHEN 5135 THEN '142638'
                       WHEN 5132 THEN '029868'
                       WHEN 5133 THEN '065433'
                       WHEN 5137 THEN '011368'
                       WHEN 5136 THEN '011370'
                       WHEN 5134 THEN     '123307'
                  END AS SC_Number,
                  CAST(s.lastName + ', ' + s.firstName AS CHAR(36)) AS 'Name',

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources