Creating view for Repoting

Question

Post reply

Creating view for Repoting

jeffrey.lowe

Grasshopper

Points: 17
More actions
June 19, 2008 at 1:18 pm

#208602

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 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

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,

Everything you can imagine is real.

jeffrey.lowe Grasshopper Points: 17 More actions · Answer 2

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!:)

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 3

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)

bledu SSChampion Points: 12335 More actions · Answer 4

if you are new to sql i'd suggest that you run the individual selects before unioning them its much easier for you to nail the problem.

oh and follow Steve advice :smooooth:, and makke sure to get a good book on sql if you can.

Everything you can imagine is real.

jeffrey.lowe Grasshopper Points: 17 More actions · Answer 5

jeffrey.lowe

Grasshopper

Points: 17

June 19, 2008 at 3:18 pm

#831439

Thanks for all of your help!!