SQL 舊有的SQL 不能用了

本文由 SunnyHo2005 在 2020-03-18 發表於 "WebSAMS 討論區" 討論區

  1. 58092860

    SunnyHo2005
    Expand Collapse

    文章:
    13
    讚:
    0
    之前用的SQL 突然不能用了,執行出現以下警告字句﹕
    The request may contains malicious code.
    Please contact the system administrator.

    請幫忙加以修改,盡量保持原有的功能﹗

    SQL 如下﹕

    select
    b. STRN,
    curr.schyear '現學年',
    curr.classcode '現班別',
    curr.classno '現學號',
    b.CHNAME '中文姓名',
    b. enname '英文姓名',

    c.CLASSNAME '班別',
    b.CLASSNO '學號',
    a.ANPTYPE,
    trim((case when trim(a.PREFIX) <> '' then trim(a.PREFIX) else '' end) + (case when trim(a.PREFIX) <> '' and (right(trim(a.PREFIX),1) between char(33) and char(126) or left(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or left(trim(h.CH_DES),1) between char(33) and char(126)) then ' ' else '' end) + (case when g.TOTAL>0 then (case when g.NONATTTYPE = 'ABSNT' then '缺席' + cast(cast(g.TOTAL as numeric(10,1)) as text) + '天' else '遲到' + cast(g.TOTAL as text) + '次' end) when a.NAAOFFEREDID>0 then (case when f.NAAPOSTCODE is null and f.NAAPERFCODE is null then i.CH_DES when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null then i.CH_DES + j.CH_DES when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null then i.CH_DES + ' (' + k.CH_DES + ')' else i.CH_DES + j.CH_DES + ' (' + k.CH_DES + ')' end) else (case when a.DESCRIPTIONCODE is not null and trim(a.DESCRIPTIONCODE) <> '' then trim(h.CH_DES) else trim(a.DESCRIPTIONSTR) end) end) + (case when trim(a.POSITION) <> '' and (right(trim(a.DESCRIPTIONSTR),1) between char(33) and char(126) or right(trim(h.CH_DES),1) between char(33) and char(126) or left(trim(a.POSITION),1) between char(33) and char(126)) then ' ' else '' end) + (case when trim(a.POSITION) <> '' then trim(a.POSITION) else '' end) + (case when d.SCHEME = 'M' then (case when a.LVL1>0 then ' ' + (case when a.ANPTYPE = 'A' then n.AWD1CHIDESC else n.PUNISH1CHIDESC end) + ' ' + cast(a.LVL1 as text) + ' 次' else '' end) + (case when a.LVL2>0 then ' ' + (case when a.ANPTYPE = 'A' then n.AWD2CHIDESC else n.PUNISH2CHIDESC end) + ' ' + cast(a.LVL2 as text) + ' 次' else '' end) + (case when a.LVL3>0 then ' ' + (case when a.ANPTYPE = 'A' then n.AWD3CHIDESC else n.PUNISH3CHIDESC end) + ' ' + cast(a.LVL3 as text) + ' 次' else '' end) + (case when a.LVL4>0 then ' ' + (case when a.ANPTYPE = 'A' then n.AWD4CHIDESC else n.PUNISH4CHIDESC end) + ' ' + cast(a.LVL4 as text) + ' 次' else '' end) + (case when a.LVL5>0 then ' ' + (case when a.ANPTYPE = 'A' then n.AWD5CHIDESC else n.PUNISH5CHIDESC end) + ' ' + cast(a.LVL5 as text) + ' 次' else '' end) else '' end)) 'EVENT_CH',
    dateformat(a.ANPDATE, 'DD/MM/YYYY') 'ANPDATE'
    from TB_STU_ANPSTUREC a
    join VW_STU_LATESTSTUDENT b
    on a.SUID = b.SUID and a.STUID = b.STUID and a.SCHYEAR = b.SCHYEAR
    join TB_SCH_SCHCLASS c
    on a.SUID = c.SUID and b.SCHYEAR = c.SCHYEAR and b.SCHLVL = c.SCHLEVEL and b.SCHSESS = c.SCHSESSION and b.CLASSLVL = c.CLASSLEVEL and b.CLASSCODE = c.CLASSCODE join TB_STU_ANPSCHEME d on a.SUID = d.SUID and a.SCHYEAR = d.SCHYEAR left outer join TB_NAA_NAAOFFERED e on a.SUID = e.SUID and a.SCHYEAR = e.SCHYEAR and a.NAAOFFEREDID = e.NAAOFFEREDID left outer join TB_NAA_STUDENTNAA f on a.SUID = f.SUID and a.STUID = f.STUID and a.NAAOFFEREDID = f.NAAOFFEREDID left outer join (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT, sum(NONATTCOUNT)'TOTAL'
    from TB_ATT_NONATT
    where ANPRECID is not null group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) g on a.SUID = g.SUID and a.SCHYEAR = g.SCHYEAR and a.STUID = g.STUID and a.ANPRECID = g.ANPRECID
    left outer join TB_HSE_COMMON h
    on a.SUID = h.SUID and a.DESCRIPTIONCODE = h.CODE_ID and ((a.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (a.ANPTYPE = 'P' and h.TB_ID = 'PSHMNT'))
    left outer join TB_HSE_COMMON i
    on a.SUID = i.SUID and e.NAACODE = i.CODE_ID and ((i.TB_ID = 'ECACD' and e.NAATYPE = 'E') or (i.TB_ID = 'INTERSCH' and e.NAATYPE = 'I') or (i.TB_ID = 'SEVPST' and e.NAATYPE = 'S'))
    left outer join TB_HSE_COMMON j
    on a.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID = 'ECAPST'
    left outer join TB_HSE_COMMON k
    on a.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID = 'ECAPFM'
    left outer join TB_HSE_COMMON l
    on a.SUID = l.SUID and a.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF' left outer join TB_HSE_COMMON m on a.SUID = m.SUID and a.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join TB_STU_ANPSCHEMEDETAILMD n
    on a.SUID = n.SUID and a.SCHYEAR = n.SCHYEAR left outer join VW_ASR_STAFF o on a.SUID = o.SUID and a.STAFFCODE = o.STAFFCODE

    join (select * from VW_STU_LATESTSTUDENT) curr
    on curr.suid=a.suid and curr.stuid=a.stuid
    where curr.schyear = ? and a.PRINTIND=1
    order by 2,3,4,7,9
     
    #1 SunnyHo2005, 2020-03-18
  2. 57873296

    edb-rn
    Expand Collapse

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

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

    select
    b. STRN,
    curr.schyear '現學年',
    curr.classcode '現班別',
    curr.classno '現學號',
    b.CHNAME '中文姓名',
    b. enname '英文姓名',
    c.CLASSNAME '班別',
    b.CLASSNO '學號',
    z.ANPTYPE,
    trim((case when trim(z.PREFIX) <> '' then trim(z.PREFIX) else '' end) + (case when trim(z.PREFIX) <> '' and (right(trim(z.PREFIX),1) between char(33) and char(126) or left(trim(z.DESCRIPTIONSTR),1) between char(33) and char(126) or left(trim(h.CH_DES),1) between char(33) and char(126)) then ' ' else '' end) + (case when g.TOTAL>0 then (case when g.NONATTTYPE = 'ABSNT' then '缺席' + cast(cast(g.TOTAL as numeric(10,1)) as text) + '天' else '遲到' + cast(g.TOTAL as text) + '次' end) when z.NAAOFFEREDID>0 then (case when f.NAAPOSTCODE is null and f.NAAPERFCODE is null then i.CH_DES when f.NAAPOSTCODE is not null and f.NAAPERFCODE is null then i.CH_DES + j.CH_DES when f.NAAPOSTCODE is null and f.NAAPERFCODE is not null then i.CH_DES + ' (' + k.CH_DES + ')' else i.CH_DES + j.CH_DES + ' (' + k.CH_DES + ')' end) else (case when z.DESCRIPTIONCODE is not null and trim(z.DESCRIPTIONCODE) <> '' then trim(h.CH_DES) else trim(z.DESCRIPTIONSTR) end) end) + (case when trim(z.POSITION) <> '' and (right(trim(z.DESCRIPTIONSTR),1) between char(33) and char(126) or right(trim(h.CH_DES),1) between char(33) and char(126) or left(trim(z.POSITION),1) between char(33) and char(126)) then ' ' else '' end) + (case when trim(z.POSITION) <> '' then trim(z.POSITION) else '' end) + (case when d.SCHEME = 'M' then (case when z.LVL1>0 then ' ' + (case when z.ANPTYPE = 'A' then n.AWD1CHIDESC else n.PUNISH1CHIDESC end) + ' ' + cast(z.LVL1 as text) + ' 次' else '' end) + (case when z.LVL2>0 then ' ' + (case when z.ANPTYPE = 'A' then n.AWD2CHIDESC else n.PUNISH2CHIDESC end) + ' ' + cast(z.LVL2 as text) + ' 次' else '' end) + (case when z.LVL3>0 then ' ' + (case when z.ANPTYPE = 'A' then n.AWD3CHIDESC else n.PUNISH3CHIDESC end) + ' ' + cast(z.LVL3 as text) + ' 次' else '' end) + (case when z.LVL4>0 then ' ' + (case when z.ANPTYPE = 'A' then n.AWD4CHIDESC else n.PUNISH4CHIDESC end) + ' ' + cast(z.LVL4 as text) + ' 次' else '' end) + (case when z.LVL5>0 then ' ' + (case when z.ANPTYPE = 'A' then n.AWD5CHIDESC else n.PUNISH5CHIDESC end) + ' ' + cast(z.LVL5 as text) + ' 次' else '' end) else '' end)) 'EVENT_CH',
    dateformat(z.ANPDATE, 'DD/MM/YYYY') 'ANPDATE'
    from TB_STU_ANPSTUREC z
    join VW_STU_LATESTSTUDENT b
    on z.SUID = b.SUID and z.STUID = b.STUID and z.SCHYEAR = b.SCHYEAR
    join TB_SCH_SCHCLASS c
    on z.SUID = c.SUID and b.SCHYEAR = c.SCHYEAR and b.SCHLVL = c.SCHLEVEL and b.SCHSESS = c.SCHSESSION and b.CLASSLVL = c.CLASSLEVEL and b.CLASSCODE = c.CLASSCODE join TB_STU_ANPSCHEME d on z.SUID = d.SUID and z.SCHYEAR = d.SCHYEAR left outer join TB_NAA_NAAOFFERED e on z.SUID = e.SUID and z.SCHYEAR = e.SCHYEAR and z.NAAOFFEREDID = e.NAAOFFEREDID left outer join TB_NAA_STUDENTNAA f on z.SUID = f.SUID and z.STUID = f.STUID and z.NAAOFFEREDID = f.NAAOFFEREDID left outer join (select SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT, sum(NONATTCOUNT)'TOTAL'
    from TB_ATT_NONATT
    where ANPRECID is not null group by SUID, SCHYEAR, STUID, ANPRECID, NONATTTYPE, NONATTCOUNT) g on z.SUID = g.SUID and z.SCHYEAR = g.SCHYEAR and z.STUID = g.STUID and z.ANPRECID = g.ANPRECID
    left outer join TB_HSE_COMMON h
    on z.SUID = h.SUID and z.DESCRIPTIONCODE = h.CODE_ID and ((z.ANPTYPE = 'A' and h.TB_ID = 'PRIZE') or (z.ANPTYPE = 'P' and h.TB_ID = 'PSHMNT'))
    left outer join TB_HSE_COMMON i
    on z.SUID = i.SUID and e.NAACODE = i.CODE_ID and ((i.TB_ID = 'ECACD' and e.NAATYPE = 'E') or (i.TB_ID = 'INTERSCH' and e.NAATYPE = 'I') or (i.TB_ID = 'SEVPST' and e.NAATYPE = 'S'))
    left outer join TB_HSE_COMMON j
    on z.SUID = j.SUID and f.NAAPOSTCODE = j.CODE_ID and j.TB_ID = 'ECAPST'
    left outer join TB_HSE_COMMON k
    on z.SUID = k.SUID and f.NAAPERFCODE = k.CODE_ID and k.TB_ID = 'ECAPFM'
    left outer join TB_HSE_COMMON l
    on z.SUID = l.SUID and z.AWARDFROM = l.CODE_ID and l.TB_ID = 'AWARDF' left outer join TB_HSE_COMMON m on z.SUID = m.SUID and z.AWARDCAT = m.CODE_ID and m.TB_ID = 'AWARDC'
    left outer join TB_STU_ANPSCHEMEDETAILMD n
    on z.SUID = n.SUID and z.SCHYEAR = n.SCHYEAR left outer join VW_ASR_STAFF o on z.SUID = o.SUID and z.STAFFCODE = o.STAFFCODE
    join (select * from VW_STU_LATESTSTUDENT) curr
    on curr.suid=z.suid and curr.stuid=z.stuid
    where curr.schyear = ? and z.PRINTIND=1
    order by 2,3,4,7,9