December 13, 2007 at 7:11 am
I am using the following code to create a sql view.
create view view_assess_list_sfl
(YEAR,
SECTOR,
CURRICULUM_MANAGER,
COURSE_LEADER,
COURSE_DETAIL,
COURSE_CODE_ONLY,
COURSE_NAME_ONLY,
BLOCK,
OCCUR,
LEVL,
STUDENT_ID,
STUDENT_JOIN_CODE,
SEQ_NO,
STATUS,
SQA_ID,
FIRST_NAME,
SURNAME,
MOA,
SUBJECT_TYPE,
SUBJECT_CATEGORY,
SUBJECT_DETAIL,
DPG,
CREDIT,
SUPERCLASS,
SUPERCLASS_TITLE,
MOD_GRP,
MOD_GRP_TITLE,
SUBJECT_CODE,
SUBJECT_TITLE,
PERIOD,
OCC,
LECTURER,
RESULT_MARK,
RESULT,
PF,
DOB,
ADD1,
ADD2,
ADD3,
ADD4,
POSTCODE,
ARCHIVE_LOCATION,
ARCHIVED_DATE,
START_WEEK,
END_WEEK,
SERVICING_DEPT,
NEW_DEPT,
OUTCOME_TITLE,
OUTCOME_MARK)
as
select sce_ayrcYEAR,
crs_dptc SECTOR,
a2.prs_surn + ', ' + a2.prs_fnm1 CURRICULUM_LEADER,
a1.prs_surn + ', ' + a1.prs_fnm1 COURSE_LEADER,
sce_crsc + sce_blok + sce_occl + ' - ' + crs_nameCOURSE,
sce_crscCOURSE_CODE,
crs_nameCOURSE_NAME,
sce_blokBLOCK,
sce_occlOCCUR,
case crs_fecm when '1' then 'HE'
when '2' then 'FE'
else 'XX'
END +
case cbo_moac when '01' then 'FT'
when '17' then 'FT'
else 'PT'
END,
stu_codeSTUDENT_ID,
sce_scjcSTUDENT_JOIN_CODE,
sce_seq2SEQ_NO,
sce_stac STATUS,
stu_scotSQA_ID,
stu_fnm1FIRST_NAME,
stu_surnSURNAME,
case sce_moac when '01' then 'Full Time'
when '05' then 'Short Full Time'
when '06' then 'Block Release'
when '07' then 'Day Release'
WHEN '08' THEN 'Other Part Time Day'
WHEN '09' THEN 'Evenings/Weekends'
WHEN '15' THEN 'Open Learning'
ELSE 'Other'
end as MOA,
A.MOD_UTYCTYPE,
CASE (substring(a.mod_code,1,1)) when 'X' then 'EXAM' when 'C' then 'COURSE' else 'OTHER' end CATEGORY,
b.mod_code + ' ' + b.psl_code + ' ' + b.mav_occur + ' - ' + mod_nameSUBJECT,
j.par_codeDPG,
a.mod_crdtCREDIT,
a.esb_codeSUPERCLASS,
h.esb_nameSUPERCLASS_TITLE,
a.mod_cogcMOD_GRP,
i.cog_nameMOD_GRP_TITLE,
b.mod_codeCODE,
mod_nameSUBJECT_TITLE,
f.psl_namePERIOD,
b.mav_occurOCC,
a3.prs_surn + ', ' + a3.prs_fnm1,
b.smr_agrgRESULT_MARK,
case b.smr_agrg when '2' then 'PASS'
when '3' then 'PASS'
when '4' then 'PASS'
when 'P' then 'PASS'
when 'C' then 'PASS'
when 'D' then 'DEFERRED'
when 'F' then 'FAIL'
when 'H' then 'HELD'
when 'M' then 'MERIT'
when 'P+' then 'PASS'
when 'P-' then 'PASS'
when 'PP' then 'FAIL'
when 'W' then 'WITHDRAWN' else 'PENDING' endRESULT,
b.smr_rsltPF,
convert(varchar(12),stu_dob)DOB, --check date format--
stu_had1 ADD1,
stu_had2 ADD2,
stu_had3 ADD3,
stu_had4 ADD4,
stu_hapc POSTCODE,
k.ayr1_codeARCHIVE_LOCATION,
k.mad_ddateDATE_ARCHIVED,
c.mav_begwSTART_WEEK,
c.mav_endwEND_WEEK,
a3.prs_dptcSERVICING_DEPT,
crs_udf9NEW_DEPT,
l.mab_nameOUTCOME_TITLE,
m.sas_agrgOUTCOME_MARK
from ins_smr b, srs_scj, ins_stu, srs_sce left outer join srs_rft on sce_rftc=rft_code, srs_crs, ins_mod a left outer join ins_esb h on a.esb_code = h.esb_code, ins_mod a4 left outer join cam_cog i on a4.mod_cogc = i.cog_code,ins_mod left outer join ins_sub j on a.esb_code =j.sub_code, cam_mav c, cam_smo d, ins_prs a1 left outer join srs_cbo on a1.prs_code=cbo_prsc, ins_prs a2 right outer join srs_crs on a2.prs_code = crs_prsc, ins_prs a3 right outer join cam_mav c on a3.prs_code = c.prs_code, ins_psl f, ins_esb h, cam_cog i, ins_sub j, cam_mad k, cam_mab l, cam_sas m
where b.spr_code = scj_sprc
and scj_stuc = stu_code
and sce_scjc = scj_code
and crs_code = sce_crsc
and b.spr_code = d.spr_code
and a.mod_code = b.mod_code
and c.mod_code = d.mod_code
and a.mod_code = c.mod_code
and b.mod_code = k.mod_code
and c.mav_occur = d.mav_occur
and b.mav_occur = c.mav_occur
and b.mav_occur = k.mav_occur
and b.ayr_code = c.ayr_code
and c.ayr_code = d.ayr_code
and b.ayr_code = k.ayr_code
and b.psl_code = c.psl_code
and c.psl_code = d.psl_code
and b.psl_code = k.psl_code
and crs_code = cbo_crsc
and cbo_ayrc = sce_ayrc
and cbo_crsc = sce_crsc
and cbo_blok = sce_blok
and cbo_occl = sce_occl
and sce_rftc = rft_code
and a1.prs_code =cbo_prsc
and a2.prs_code = crs_prsc
and a3.prs_code = c.prs_code
and f.psl_code = b.psl_code
and a.esb_code = h.esb_code
and a.mod_cogc = i.cog_code
and a.esb_code = j.sub_code
and m.spr_code = b.spr_code
and m.mod_code = b.mod_code
and m.mav_occur = b.mav_occur
and m.ayr_code = b.ayr_code
and m.psl_code = b.psl_code
and m.mod_code = c.mod_code
and m.mav_occur = c.mav_occur
and m.ayr_code = c.ayr_code
and m.psl_code = c.psl_code
and m.spr_code = d.spr_code
and m.mod_code = d.mod_code
and m.mav_occur = d.mav_occur
and m.ayr_code = d.ayr_code
and m.psl_code = d.psl_code
and m.mab_seq = l.mab_seq
and m.map_code = l.map_code
and k.mod_code = m.mod_code
and k.mab_seq = m.mab_seq
and sce_ayrc in ('04/05','05/06', '06/07') -- check the date formats --
and coalesce(crs_udf9,crs_dptc) = 'SL'
However it returns errors as follows
Msg 4104, Level 16, State 1, Procedure view_assess_list_sfl, Line 52
The multi-part identifier "a.esb_code" could not be bound.
Msg 1013, Level 16, State 1, Procedure view_assess_list_sfl, Line 52
The objects "srs_crs" and "srs_crs" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
how do i resolve this?
December 13, 2007 at 7:26 am
Whenever I get this error, I break it down by selecting from primary table, then adding joins one-by-one until the error. Check the following as you do this:
- derived tables are aliased
- all columns have correct names in and outside derived tables (if any referenced)
- check all prefixes (qualifiers)
December 13, 2007 at 7:56 am
I think you're going to have issues with this view. I'd really look at tackling this another way, maybe breaking the information down, using a stored procedure to get parts back, etc. This feels like a report view and there might be better ways.
However, if you need this to be one query-able construct. Build some smaller views and then make a view on top of them. In something this complex, no one will decode it easily, so I'd take a couple tables and make a view_xxx on them, then include that view in the query here.
As mentioned above, break it down into smaller chunks and work with it. A view is a select statement
------
create view myview
as
select x, y, z
from TableA
----
that's it. So get the select working and add the CREATE VIEW.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply