SQL error in executing SQL

本文由 loky2020 在 2020-02-07 發表於 "WebSAMS 討論區" 討論區

  1. 55169138

    loky2020
    Expand Collapse

    文章:
    1
    讚:
    0
    WEBSAMS > Security Error
    The request may contains malicious code.
    Please contact the system administrator.

    select
    a.classcode '班別',
    a.classno '班號',
    a.chname '中文姓名',
    a.enname '英文姓名',
    a.regno '學號',
    a.strn 'STRN',
    a.sex '性別',
    a.hometel '住宅電話',
    a.mobileno '學生手機',
    (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) '中文地址',
    b.chname '父親姓名',
    b.occupation '父親職業',
    b.phone '父親聯絡電話',
    b.emergencyphone '父親緊急聯絡電話',
    c.chname '母親姓名',
    c.occupation '母親職業',
    c.phone '母親聯絡電話',
    c.emergencyphone '母親緊急聯絡電話',
    d.chname '監護人姓名',
    d.occupation '監護人職業',
    d.phone '監護人聯絡電話',
    d.emergencyphone '監護人緊急聯絡電話'
    from VW_STU_LATESTSTUDENT a
    left outer join tb_stu_parent b
    on a.suid=b.suid and a.stuid=b.stuid and b.relation='01'
    left outer join tb_stu_parent c
    on a.suid=c.suid and a.stuid=c.stuid and c.relation='02'
    left outer join tb_stu_parent d
    on a.suid=d.suid and a.stuid=d.stuid and d.guardianind='1'
    where a.schyear=?
    order by classcode,a.classno
     
    #1 loky2020, 2020-02-07
  2. 57873296

    edb-rn
    Expand Collapse

    文章:
    190
    讚:
    0
    老師,請參考以下,

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

    select
    z.classcode '班別',
    z.classno '班號',
    z.chname '中文姓名',
    z.enname '英文姓名',
    z.regno '學號',
    z.strn 'STRN',
    z.sex '性別',
    z.hometel '住宅電話',
    z.mobileno '學生手機',
    (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) '中文地址',
    b.chname '父親姓名',
    b.occupation '父親職業',
    b.phone '父親聯絡電話',
    b.emergencyphone '父親緊急聯絡電話',
    c.chname '母親姓名',
    c.occupation '母親職業',
    c.phone '母親聯絡電話',
    c.emergencyphone '母親緊急聯絡電話',
    d.chname '監護人姓名',
    d.occupation '監護人職業',
    d.phone '監護人聯絡電話',
    d.emergencyphone '監護人緊急聯絡電話'
    from VW_STU_LATESTSTUDENT z
    left outer join tb_stu_parent b
    on z.suid=b.suid and z.stuid=b.stuid and b.relation='01'
    left outer join tb_stu_parent c
    on z.suid=c.suid and z.stuid=c.stuid and c.relation='02'
    left outer join tb_stu_parent d
    on z.suid=d.suid and z.stuid=d.stuid and d.guardianind='1'
    where z.schyear=?
    order by classcode,z.classno
     

    附件文件: