January 16, 2008 at 4:36 am
I have a view called view_badge.
The sql to create this view is displayed below.
The following line is the one giving problems
and scj_eend = (select (max(isnull(scj_eend,cbo_endd)))
where the latest scj_eend or cbo_end (end date is not being brought across)
However i can't see a problem with the sql.
any tips would be appreciated.
create view view_badge
as
selectscj_ayrcYEAR,
a.stu_codeSTU_CODE,
scj_codeJOIN_CODE,
a.stu_fnm1FORENAME1,
a.stu_fnm2FORENAME2,
a.stu_surnSURNAME,
a.stu_dobDOB,
scj_crsc + '' + scj_blok + '' + scj_occlCOURSE,
max(c.scj_eend)END_DATE
from ins_stu a , srs_scj c, srs_cbo b
where a.stu_code = scj_stuc
and b.cbo_ayrc = scj_ayrc
and b.cbo_crsc = scj_crsc
and b.cbo_blok = scj_blok
and b.cbo_occl = scj_occl
and b.cbo_ayrc = '07/08'
and b.cbo_crsc not in ('CLEARUP','OTHZCRUP')
and scj_code = (select min(scj_code)
from srs_scj z, srs_cbo y
where z.scj_stuc = c.scj_stuc
and z.scj_ayrc = y.cbo_ayrc
and z.scj_crsc = y.cbo_crsc
and z.scj_blok = y.cbo_blok
and z.scj_occl = y.cbo_occl
and scj_eend = (select (max(isnull(scj_eend,cbo_endd)))
from srs_scj , srs_cbo
where scj_stuc = z.scj_stuc
and scj_ayrc = cbo_ayrc
and scj_crsc = cbo_crsc
and scj_blok = cbo_blok
and scj_occl = cbo_occl))
group by a.stu_code,
scj_ayrc,
scj_code,
a.stu_fnm1,
a.stu_fnm2,
a.stu_surn,
a.stu_dob,
scj_crsc + '/' + scj_blok + '/' + scj_occl
January 16, 2008 at 5:15 am
OK If I read this right you are after the highest date from one of two columns column in a linked table.
max(c.scj_eend) END_DATE
from ins_stu a , srs_scj c, srs_cbo b
where a.stu_code = scj_stuc
If you run the sub query on its own does it work?
Have you tried specifying joins in the format INNER JOIN table t on T.ID=F.ID?
I know it sounds daft but rearranging your SQL into a more presentable format often helps highlight issues.
I might be missing something obvious but its a place to start.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply