SQL 舊有的SQL不能用

select

b.classname '班別',

a.CLASSLVL '級別',

a.classno '學號',

a.chname '中文姓名',

a.enname '英文姓名',

a.regno '學生註冊編號',

a.hkid '身份證號碼',

a.pob '出生地點',

dateformat(a.dob, 'DD/MM/YYYY') as dob,

a.sex '性別',

a.MOBILENO '學生手提電話',

a.email '學生電郵',

d.chname as 監護人姓名 ,

d.OCCUPATION '監護人職業',

a.hometel '住宅電話',

d.EMERGENCYPHONE '緊急聯絡電話',

(case when e.ch_des is not null then e.ch_des else '' end) +

(case when trim(a.chdistrict)<>'' then trim(a.chdistrict) else '' end)+

(case when trim(a.chstreet)<>'' then trim(a.chstreet) else '' end)+

(case when trim(a.chvillageestate)<>'' then trim(a.chvillageestate) else '' end)+

(case when trim(a.chbuilding)<>'' then trim(a.chbuilding) else '' end)+

(case when trim(a.chblkno)<>'' then trim(a.chblkno)+'座' else '' end)+

(case when trim(a.chfloorno)<>'' then trim(a.chfloorno)+'層' else '' end)+

(case when trim(a.chflatno)<>'' then trim(a.chflatno)+'室' else '' end) as address

from vw_stu_lateststudent a

left outer join tb_sch_schclass b

on b.classcode=a.classcode and

b.schyear=a.schyear and

b.suid = a.suid

left outer join tb_hse_common c

on c.code_id=a.schhouse and

c.tb_id='SCHHUS' and

c.suid = a.suid

left outer join tb_stu_parent d

on d.stuid = a.stuid and

d.guardianind='1' and

d.suid = a.suid

left outer join tb_hse_common e

on e.code_id=a.areacode and

e.tb_id='AREACD' and

e.suid = a.suid

where a.schyear=?

order by a.classcode, a.classno

 

老師，請參考以下，

只需要把table 的alias a. 改為 z.
就可以成功抽取資料。

select
b.classname '班別',
z.CLASSLVL '級別',
z.classno '學號',
z.chname '中文姓名',
z.enname '英文姓名',
z.regno '學生註冊編號',
z.hkid '身份證號碼',
z.pob '出生地點',
dateformat(z.dob, 'DD/MM/YYYY') as dob,
z.sex '性別',
z.MOBILENO '學生手提電話',
z.email '學生電郵',
d.chname as 監護人姓名 ,
d.OCCUPATION '監護人職業',
z.hometel '住宅電話',
d.EMERGENCYPHONE '緊急聯絡電話',
(case when e.ch_des is not null then e.ch_des else '' end) +
(case when trim(z.chdistrict)<>'' then trim(z.chdistrict) else '' end)+
(case when trim(z.chstreet)<>'' then trim(z.chstreet) else '' end)+
(case when trim(z.chvillageestate)<>'' then trim(z.chvillageestate) else '' end)+
(case when trim(z.chbuilding)<>'' then trim(z.chbuilding) else '' end)+
(case when trim(z.chblkno)<>'' then trim(z.chblkno)+'座' else '' end)+
(case when trim(z.chfloorno)<>'' then trim(z.chfloorno)+'層' else '' end)+
(case when trim(z.chflatno)<>'' then trim(z.chflatno)+'室' else '' end) as address
from vw_stu_lateststudent z
left outer join tb_sch_schclass b
on b.classcode=z.classcode and
b.schyear=z.schyear and
b.suid = z.suid
left outer join tb_hse_common c
on c.code_id=z.schhouse and
c.tb_id='SCHHUS' and
c.suid = z.suid
left outer join tb_stu_parent d
on d.stuid = z.stuid and
d.guardianind='1' and
d.suid = z.suid
left outer join tb_hse_common e
on e.code_id=z.areacode and
e.tb_id='AREACD' and
e.suid = z.suid
where z.schyear=?
order by z.classcode, z.classno