Creating view for Repoting
-
I am very new to using SQL, I need some help in creating a DataBase view. I keep getting this error:Server: Msg 170, Level 15, State 1, Procedure V_TRANSCRIPTS_SA2, Line 13
Line 13: Incorrect syntax near '.'.
Please also see script below:
CREATE VIEW DBO.V_TRANSCRIPTS_SA2
as
--class enrollments
select
s.student_id,
s.student_fname,
s.student_lname,
s.registered_date,
CF.CustFieldValue as 'Hire_Date',
dbo.getMemberFullName(s.student_lname, s.student_fname, s.student_mi) student_name,
dbo.getFullStatus(s.student_status) student_status,
s.organization_id,
tbo.NAME organization_name,
dbo.getJobsQualifiedFor(s.student_id) jobs_qualified_for,
se.objectid objectid,
se.signupdate,
se.starteddate,
se.completeddate,
se.COMPLETEDPERCENT,
dbo.getScore(co.DeliveryMethod, co.thresholdtype, co.gradingmethod, se.inprogressstatus, convert(varchar, se.SCORE)) as score,
dbo.getGrade(co.DeliveryMethod, co.thresholdtype, co.gradingmethod, se.inprogressstatus, se.GRADE) as grade,
se.status enrollment_status,
c.TITLE,
null type,
'Course' object_type,
dbo.getOfferingListByCourse(c.CourseId)offering,
dbo.isPassed(co.gradingmethod,co.thresholdvalue,se.score,se.grade,se.status) pass_fail,
c.organizationid as obj_organizationid
from TBL_ORGANIZATION tbo,TBL_USERREGISTRATION se,
TBL_COURSE c,TBL_COURSEOFFERING co,STUDENT s,
TBL_CLASSOFFERING cls,dbo.tbl_custfieldvalue
INNER JOIN dbo.tbl_custfieldvalue CF1 on ( S.STUDENT_ID =CF1.ObjectId and CF1.Fieldn = 1)
INNER JOIN dbo.tbl_custfieldvalue CF on (S.STUDENT_ID = CF.ObjectId and CF.Fieldn = 2)
where tbo.organizationid = s.organization_id
and se.studentid = s.student_id
and se.objecttypeid = 75
and c.courseid = co.courseid
and se.OBJECTID = cls.classofferingid and
cls.courseofferingid = co.courseofferingid
and se.status = 'C'
union all
-- Online/ Offlines Enrollment
select
s.student_id,
s.student_fname,
s.student_lname,
s.registered_date,
CF.CustFieldValue as 'Hire_Date',
dbo.getMemberFullName(s.student_lname, s.student_fname, s.student_mi) student_name,
dbo.getFullStatus(s.student_status) student_status,
s.organization_id,
tbo.NAME organization_name,
dbo.getJobsQualifiedFor(s.student_id) jobs_qualified_for,
se.objectid objectid,
se.signupdate,
se.starteddate,
se.completeddate,
se.COMPLETEDPERCENT,
dbo.getScore(crs.DeliveryMethod, crs.thresholdtype, crs.gradingmethod, se.inprogressstatus, convert(varchar, se.SCORE)) as score,
dbo.getGrade(crs.DeliveryMethod, crs.thresholdtype, crs.gradingmethod, se.inprogressstatus, se.GRADE) as grade,
se.status enrollment_status,
c.TITLE,
null type,
'Course' object_type,
dbo.getOfferingListByCourse(c.CourseId)offering,
dbo.isPassed(crs.gradingmethod,crs.thresholdvalue,se.score,se.grade,se.status) pass_fail,
c.organizationid as obj_organizationid
from TBL_ORGANIZATION tbo,TBL_USERREGISTRATION se,TBL_COURSEOFFERING crs, TBL_COURSE c,STUDENT s
INNER JOIN dbo.tbl_custfieldvalue CF1 on (S.Student_ID = CF1.ObjectId and CF1.Fieldn = 1)
INNER JOIN dbo.tbl_custfieldvalue CF on (S.Student_ID = CF.ObjectId and CF.Fieldn = 2)
where tbo.organizationid = s.organization_id
and se.studentid = s.student_id
and se.objecttypeid = 77
and se.OBJECTID = crs.COURSEOFFERINGID
and crs.courseid = c.courseid
and se.status in ('C', 'P', 'F')
union all
--curriculum enrollments
select
s.student_id,
s.student_fname,
s.student_lname,
s.registered_date,
CF.CustFieldValue as 'Hire_Date',
dbo.getMemberFullName(s.student_lname, s.student_fname, s.student_mi) student_name,
dbo.getFullStatus(s.student_status) student_status,
s.organization_id,
tbo.NAME organization_name,
dbo.getJobsQualifiedFor(s.student_id) jobs_qualified_for,
se.objectid objectid,
se.signupdate,
se.starteddate,
se.completeddate,
se.COMPLETEDPERCENT,
'' as SCORE,
'' as GRADE,
se.status,
cu.title,
dbo.getCurriculumType(cu.curriculumType) type,
'Curriculum' object_type,
'' offering,
null,
cu.organizationid as obj_organizationid
from TBL_ORGANIZATION tbo,TBL_USERREGISTRATION se,TBL_CURRICULUM cu,STUDENT s
INNER JOIN dbo.tbl_custfieldvalue CF1 on (S.Student_ID = CF1.ObjectId and CF1.Fieldn = 1)
INNER JOIN dbo.tbl_custfieldvalue CF on (S.Student_ID = CF.ObjectId and CF.Fieldn = 2)
where tbo.organizationid = s.organization_id
and se.studentid = s.student_id
and se.objecttypeid = 80
and se.objectId = cu.curriculumId
and se.status = 'C'
union all
--education plans enrollments
select
s.student_id,
s.student_fname,
s.student_lname,
s.registered_date,
CF.CustFieldValue as 'Hire_Date',
dbo.getMemberFullName(s.student_lname, s.student_fname, s.student_mi) student_name,
dbo.getFullStatus(s.student_status) student_status,
s.organization_id,
tbo.NAME organization_name,
dbo.getJobsQualifiedFor(s.student_id) jobs_qualified_for,
se.objectid objectid,
se.signupdate,
se.starteddate,
se.completeddate,
se.COMPLETEDPERCENT,
'' as SCORE,
'' as GRADE,
se.status,
ep.title,
(case ep.Type when 'T' then 'public'
when 'P' then 'private'
end) type,
'Education Plan' object_type,
'' offering,
null,
ep.organizationid as obj_organizationid
from TBL_ORGANIZATION tbo,TBL_USERREGISTRATION se,TBL_EDUPLAN ep,STUDENT s
INNER JOIN dbo.tbl_custfieldvalue CF1 on (S.Student_ID = CF1.ObjectId and CF1.Fieldn = 1)
INNER JOIN dbo.tbl_custfieldvalue CF on (S.Student_ID = CF.ObjectId and CF.Fieldn = 2)
where tbo.organizationid = s.organization_id
and se.studentid = s.student_id
and se.objecttypeid = 79
and se.objectId = ep.eduplanid
and se.status = 'C'
Please Help!!
Thanks-J
-
it looks like you are missing the AS on your aliasing
dbo.getMemberFullName(s.student_lname, s.student_fname, s.student_mi) student_name,
dbo.getFullStatus(s.student_status) student_status,
s.organization_id,
tbo.NAME organization_name,
should be
dbo.getMemberFullName(s.student_lname, s.student_fname, s.student_mi) AS student_name,
dbo.getFullStatus(s.student_status) AS student_status,
s.organization_id,
tbo.NAME AS organization_name,
-
I tried what what you said and I get the following error:
Server: Msg 156, Level 15, State 1, Procedure V_TRANSCRIPTS_SA2, Line 33
Incorrect syntax near the keyword 'INNER'.
Thanks for you help!:)
-
Change all your tables in the FROM clause to
FROM tablea inner join tableb
on tablea.x = tableb.x
The mixed join types might cause issues (ANSI and non-ANSI)
-
Thanks for all of your help!!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply