ORDER BY 的时候不能在select 句子中用sequence

SELECT seq_com_id.NEXTVAL delta_id,XX.* FROM (
   SELECT 
    'EXT' delta_type, NULL paper_no,
            'D10041' err_cd, 'SYSTEM' created_by, SYSDATE created_dt,
            'SYSTEM' updated_by, SYSDATE updated_dt, NULL deleted_by,
            NULL deleted_dt, 1 VERSION, x.exam_yr, x.exam_lvl_cd,
            x.exam_series_cd, x.index_no, x.subj_cd
       FROM (SELECT DISTINCT a.exam_yr, a.exam_lvl_cd, a.exam_series_cd,
                             a.index_no, a.uin, a.reg_stat_nm, a.dob,
                             b.subj_cd
                        FROM xe_rp_cddt_reg a,
                             xe_cddt_posting_paper b,
                             xe_subj_configuration c
                       WHERE a.exam_yr = 2008
                         AND a.exam_lvl_cd = 'GCEA'
                         AND a.exam_series_cd = 'YE'
                         AND (a.reg_st = 'N' OR a.reg_st = 'R')
                         AND a.cddt_reg_id = b.cddt_reg_id
                         AND a.exam_yr = b.exam_yr
                         AND a.exam_lvl_cd = b.exam_lvl_cd
                         AND a.exam_series_cd = b.exam_series_cd
                         AND a.exam_yr = c.exam_yr
                         AND a.exam_lvl_cd = c.exam_lvl_cd
                         AND a.exam_series_cd = c.exam_series_cd
                         AND b.subj_cd = c.subj_cd
                         AND b.lang_med_cd = c.lang_med_cd
                         AND (    c.examining_agency_cd <> 'CAMB'
                              AND c.examining_agency_cd <> 'SPORE'
                             )) x
      WHERE NOT EXISTS (
               SELECT *
                 FROM xe_rp_ext_result y
                WHERE y.exam_yr = x.exam_yr
                  AND y.exam_lvl_cd = x.exam_lvl_cd
                  AND y.uin = x.uin
                  AND y.subj_cd = x.subj_cd)
   ORDER BY uin
   ) XX

评论

1 楼 LinttleLife 2008-11-06  

还可以这样order by upper(a.nat_grp_nm)