SQL 原有sql 顯示error message

本文由 偉年 在 2020-01-21 發表於 "WebSAMS 討論區" 討論區

  1. 55061472

    偉年
    Expand Collapse

    文章:
    12
    讚:
    0
    你好,

    嘗試把table 的alias 是 a. 改為 z.
    而 wsadmin.VW_STU_LATESTSTUDENT a 已改為 wsadmin.VW_STU_LATESTSTUDENT z
    也不能抽取資料﹐請幫忙看看以下 SQL 出現子甚麼問題。

    謝謝

    select
    z.CLASScode '班別',
    z.CLASSNO '學號',
    z.STRN '學生編號',
    z.REGNO '註冊編號',
    z.CHNAME '中文姓名',
    z.ENNAME '英文姓名',
    z.SEX '性別',
    z.POB '出生地點',
    n.ch_des '國籍',
    dateformat(z.DOB, 'DD/MM/YYYY') '出生日期',
    b.ch_des '宗教',
    z.PLACEOFORIGIN '籍貫',
    z.BIRTHCERT '出生證明書',
    z.HKID '身份證',
    c.ch_des '身份證明文件類型',
    z.DOCNO '身份證明文件號碼',
    (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) '家居地址',
    z.HOMETEL '家居電話',
    z.SCHFROM '以前就讀學校',
    f.chname '父親姓名',
    f.EMERGENCYPHONE '父親緊急電話',
    f.occupation '父親職業',
    m.chname '母親姓名',
    m.EMERGENCYPHONE '母親緊急電話',
    m.occupation '母親職業',
    (select list(string(SIBC.CLASSNAME, ' ', SIB.CHNAME), ', ')
    from VW_STU_LATESTSTUDENT SIB
    left outer join TB_SCH_SCHCLASS SIBC
    on SIBC.SUID=SIB.SUID and SIBC.SCHYEAR=SIB.SCHYEAR and SIBC.SCHLEVEL=SIB.SCHLVL and SIBC.SCHSESSION=SIB.SCHSESS and SIBC.CLASSLEVEL=SIB.CLASSLVL and SIBC.CLASSCODE=SIB.CLASSCODE
    where SIB.SUID=z.SUID and SIB.SCHYEAR=z.SCHYEAR and SIB.SIBGRP<>-1 and SIB.SIBGRP=z.SIBGRP and SIB.STUID<>z.STUID) '兄弟姊妹/班別'
    from VW_STU_LATESTSTUDENT z
    left outer join TB_STU_PARENT m
    on z.SUID = m.SUID and z.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f
    on z.SUID = f.SUID and z.STUID = f.STUID and f.RELATION = '01'
    left outer join TB_HSE_COMMON b
    on z.SUID=b.SUID and b.CODE_ID=z.RELIGION and b.TB_ID='RELIG'
    left outer join TB_HSE_COMMON c
    on z.SUID=c.SUID and c.CODE_ID=a.doctype and c.TB_ID='TVLDOC'
    left outer join TB_HSE_COMMON n
    on z.SUID=n.SUID and n.CODE_ID=z.Nationality and n.TB_ID='NATION'
    where z.SCHYEAR=? and z.CLASSLVL=?
    order by z.CLASSLVL, z.CLASSCODE, z.CLASSNO
     
  2. 58521906

    edb-catherinewschan
    Expand Collapse

    文章:
    144
    讚:
    0
    你好,

    改漏了一個
    a.doctype 應改為 z.doctype , 請自行更改再嘗試執行。謝謝


    select
    z.CLASScode '班別',
    z.CLASSNO '學號',
    z.STRN '學生編號',
    z.REGNO '註冊編號',
    z.CHNAME '中文姓名',
    z.ENNAME '英文姓名',
    z.SEX '性別',
    z.POB '出生地點',
    n.ch_des '國籍',
    dateformat(z.DOB, 'DD/MM/YYYY') '出生日期',
    b.ch_des '宗教',
    z.PLACEOFORIGIN '籍貫',
    z.BIRTHCERT '出生證明書',
    z.HKID '身份證',
    c.ch_des '身份證明文件類型',
    z.DOCNO '身份證明文件號碼',
    (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) '家居地址',
    z.HOMETEL '家居電話',
    z.SCHFROM '以前就讀學校',
    f.chname '父親姓名',
    f.EMERGENCYPHONE '父親緊急電話',
    f.occupation '父親職業',
    m.chname '母親姓名',
    m.EMERGENCYPHONE '母親緊急電話',
    m.occupation '母親職業',
    (select list(string(SIBC.CLASSNAME, ' ', SIB.CHNAME), ', ')
    from VW_STU_LATESTSTUDENT SIB
    left outer join TB_SCH_SCHCLASS SIBC
    on SIBC.SUID=SIB.SUID and SIBC.SCHYEAR=SIB.SCHYEAR and SIBC.SCHLEVEL=SIB.SCHLVL and SIBC.SCHSESSION=SIB.SCHSESS and SIBC.CLASSLEVEL=SIB.CLASSLVL and SIBC.CLASSCODE=SIB.CLASSCODE
    where SIB.SUID=z.SUID and SIB.SCHYEAR=z.SCHYEAR and SIB.SIBGRP<>-1 and SIB.SIBGRP=z.SIBGRP and SIB.STUID<>z.STUID) '兄弟姊妹/班別'
    from VW_STU_LATESTSTUDENT z
    left outer join TB_STU_PARENT m
    on z.SUID = m.SUID and z.STUID = m.STUID and m.RELATION = '02'
    left outer join TB_STU_PARENT f
    on z.SUID = f.SUID and z.STUID = f.STUID and f.RELATION = '01'
    left outer join TB_HSE_COMMON b
    on z.SUID=b.SUID and b.CODE_ID=z.RELIGION and b.TB_ID='RELIG'
    left outer join TB_HSE_COMMON c
    on z.SUID=c.SUID and c.CODE_ID=a.doctype and c.TB_ID='TVLDOC'
    left outer join TB_HSE_COMMON n
    on z.SUID=n.SUID and n.CODE_ID=z.Nationality and n.TB_ID='NATION'
    where z.SCHYEAR=? and z.CLASSLVL=?
    order by z.CLASSLVL, z.CLASSCODE, z.CLASSNO
     
    #2 edb-catherinewschan, 2020-01-22