SQL does not work after upgrade to WS 2.0

本文由 P 在 2010-04-19 發表於 "WebSAMS 討論區" 討論區

  1. 55078230

    P
    Expand Collapse

    文章:
    1
    讚:
    0

    I use the following query to get student data.

    SELECT TB_STU_STUSCHREC.SCHYEAR, TB_STU_STUSCHREC.STUID, TB_STU_STUSCHREC.CLASSLVL, TB_STU_STUSCHREC.CLASSNO, TB_STU_STUSCHREC.CLASSCODE, TB_STU_STUDENT.REGNO, TB_STU_STUDENT.STRN, TB_STU_STUDENT.ENNAME, TB_STU_STUDENT.CHNAME, TB_STU_STUDENT.SEX, TB_STU_STUDENT.HOMETEL, TB_STU_STUDENT.HKID, TB_STU_STUDENT.SCHHOUSE, DATEFORMAT(TB_STU_STUDENT.DOB, 'YYYY-MM-DD') AS DOB, TB_STU_STUDENT.DISTRICTCOUNCIL, TB_STU_STUDENT.SCHLEAVERSIGN, TB_STU_STUDENT.POB, TB_STU_STUDENT.NATIONALITY, TB_STU_STUDENT.RELIGION, TB_STU_STUDENT.ETHNICITY, TB_STU_STUDENT.HOMELANGUAGE, TB_STU_STUDENT.ENFLATNO, TB_STU_STUDENT.ENFLOORNO, TB_STU_STUDENT.ENBLKNO, TB_STU_STUDENT.ENBUILDING, TB_STU_STUDENT.ENVILLAGEESTATE, TB_STU_STUDENT.ENSTREET, TB_STU_STUDENT.ENDISTRICT, TB_STU_STUDENT.AREACODE,
    TB_STU_PARENT.ENNAME AS ParentEnName
    FROM TB_STU_STUSCHREC, TB_STU_STUDENT
    LEFT OUTER JOIN TB_STU_PARENT
      ON TB_STU_STUSCHREC.STUID = TB_STU_PARENT.STUID
    WHERE TB_STU_STUSCHREC.SCHYEAR = '2009' AND TB_STU_STUSCHREC.STUID = TB_STU_STUDENT.STUID AND TB_STU_STUDENT.SCHLEAVERSIGN = '0' ORDER BY TB_STU_STUSCHREC.CLASSCODE ASC, TB_STU_STUDENT.SEX DESC, TB_STU_STUDENT.ENNAME ASC

     

    I worked well in WS 1.0 but not now in WS 2.0. Please help.

    Thanks.

     
  2. 56127239

    EDB-Wayne
    Expand Collapse

    文章:
    0
    讚:
    0

    Please try the below SQL which has been rewritten:

    SELECT
       TB_STU_STUSCHREC.SCHYEAR,
       TB_STU_STUSCHREC.STUID,
       TB_STU_STUSCHREC.CLASSLVL,
       TB_STU_STUSCHREC.CLASSNO,
       TB_STU_STUSCHREC.CLASSCODE,
       TB_STU_STUDENT.REGNO,
       TB_STU_STUDENT.STRN,
       TB_STU_STUDENT.ENNAME,
       TB_STU_STUDENT.CHNAME,
       TB_STU_STUDENT.SEX,
       TB_STU_STUDENT.HOMETEL,
       TB_STU_STUDENT.HKID,
       TB_STU_STUDENT.SCHHOUSE,
       DATEFORMAT(TB_STU_STUDENT.DOB, 'YYYY-MM-DD') AS DOB,
       TB_STU_STUDENT.DISTRICTCOUNCIL,
       TB_STU_STUDENT.SCHLEAVERSIGN,
       TB_STU_STUDENT.POB,
       TB_STU_STUDENT.NATIONALITY,
       TB_STU_STUDENT.RELIGION,
       TB_STU_STUDENT.ETHNICITY,
       TB_STU_STUDENT.HOMELANGUAGE,
       TB_STU_STUDENT.ENFLATNO,
       TB_STU_STUDENT.ENFLOORNO,
       TB_STU_STUDENT.ENBLKNO,
       TB_STU_STUDENT.ENBUILDING,
       TB_STU_STUDENT.ENVILLAGEESTATE,
       TB_STU_STUDENT.ENSTREET,
       TB_STU_STUDENT.ENDISTRICT,
       TB_STU_STUDENT.AREACODE,
       TB_STU_PARENT.ENNAME AS ParentEnName
    FROM TB_STU_STUSCHREC
    JOIN TB_STU_STUDENT
       ON TB_STU_STUSCHREC.SUID=TB_STU_STUDENT.SUID AND TB_STU_STUSCHREC.STUID=TB_STU_STUDENT.STUID
    LEFT OUTER JOIN TB_STU_PARENT
      ON TB_STU_STUSCHREC.STUID = TB_STU_PARENT.STUID
    WHERE TB_STU_STUSCHREC.SCHYEAR = '2009' AND TB_STU_STUDENT.SCHLEAVERSIGN = '0'
    ORDER BY TB_STU_STUSCHREC.CLASSCODE ASC, TB_STU_STUDENT.SEX DESC, TB_STU_STUDENT.ENNAME ASC

    This problem might occurr if commas and "outer join" are used together in Sybase 10. Please refer to this document section 2.1.2

     
    #2 EDB-Wayne, 2010-04-19
  3. 55078230

    P
    Expand Collapse

    文章:
    1
    讚:
    0
    Thanks a lot.