SELECT a.ghid00,
         a.ghh000,
         a.brid00,
         a.xm0000,
         a.xb0000,
         NVL (c.ksrq00, a.ghrq00) ghrq00,
         c.jsrq00,
         c.kssj00,
         c.kssj00,
         a.ghks00,
         a.ghyxm0,
         SUBSTR (sf_sf_csrqtonl (a.csrq00), 1, 100) brnl00,
         b.bmmc00,
         a.lgh000,
         NVL (
             (SELECT NVL (h.zdmc00, g.jbmc00)
                FROM xt_icd900 g, ys_brzdxx h
               WHERE     h.zzdbz0 = '3'
                     AND a.ghid00 = h.ghid00
                     AND g.icd900(+) = h.icd900
                     AND ROWNUM = 1),
             (SELECT NVL (h.zdmc00, g.jbmc00)
                FROM xt_icd900 g, ys_brzdxx h
               WHERE     a.ghid00 = h.ghid00
                     AND g.icd900(+) = h.icd900
                     AND ROWNUM = 1))
             AS zdmc00,
         c.jzlx00,
         DECODE (c.jzlx00,  '0', '监护',  '1', '抢救',  '2', '留观') jzlxmc
    FROM sf_brxxb0 a, bm_bmbm00 b, ys_lgbrdj c
   WHERE     ROWNUM <= 2000
         AND c.ghid00(+) = a.ghid00
         AND a.ghks00 = b.bmbh00
         AND a.ghlb00 <> -99999
         AND jsrq00 = '20991231'
ORDER BY a.ghh000 DESC