范例（cnmq001）：

原：每次查询都把数据全部查询到临时表后再筛选；

现：查询到临时表的时候，增加可行的筛选条件，再二次筛选临时表的数据，进行优化；

1）临时表创建：

PRIVATE FUNCTION cnmq001_cr_tmp()

DROP TABLE cnmq001_tmp
CREATE TEMP TABLE cnmq001_tmp(
nmbc002 LIKE nmbc_t.nmbc002,
nmbc002_desc LIKE type_t.chr500,
nmbc005 LIKE nmbc_t.nmbc005,
nmbcdocno LIKE nmbc_t.nmbcdocno,
nmbcseq LIKE nmbc_t.nmbcseq,
nmbc006 LIKE nmbc_t.nmbc006,
nmbc007 LIKE nmbc_t.nmbc007,
nmbc007_desc LIKE nmajl_t.nmajl003,
nmbcud001 LIKE nmbc_t.nmbcud001,
nmbc003 LIKE nmbc_t.nmbc003,
nmbc003_desc LIKE type_t.chr80,
inamt LIKE type_t.num20_6,
nmbc100 LIKE nmbc_t.nmbc100,
nmbc101 LIKE nmbc_t.nmbc101,
outamt LIKE type_t.num20_6,
nmbccrtid LIKE nmbc_t.nmbccrtid,
nmbccrtid_desc LIKE type_t.chr30,
nmbccrtdt LIKE nmbc_t.nmbccrtdt
)
BEGIN WORK #-# 放到事务里
CALL cnmq001_ins_tmp()
COMMIT WORK
END FUNCTION

2）TIPTOP没有直接的split分割函数，所以走点弯路：

PRIVATE FUNCTION split_itemcount(p_arg1,p_arg2)
DEFINE p_arg1 STRING
DEFINE p_arg2 STRING
DEFINE l_cnt INT
DEFINE tok BASE.StringTokenizer

#LET tok = base.StringTokenizer.create(p\_arg1,p\_arg2)
LET tok = base.StringTokenizer.createExt(p\_arg1,p\_arg2,"",TRUE)
LET l\_cnt = tok.countTokens()
RETURN l\_cnt

END FUNCTION

PRIVATE FUNCTION split(p_arg1,p_arg2,p_arg3)
DEFINE p_arg1 STRING
DEFINE p_arg2 STRING
DEFINE p_arg3 INT
DEFINE l_cnt INT
DEFINE l_idx INT
DEFINE l_retstr STRING
DEFINE l_str STRING
DEFINE tok BASE.StringTokenizer

INITIALIZE l\_retstr TO NULL
#LET tok = base.StringTokenizer.create(p\_arg1,p\_arg2)
LET tok = base.StringTokenizer.createExt(p\_arg1,p\_arg2,"",TRUE)
LET l\_cnt = tok.countTokens()
IF (p\_arg3 <= ) OR (l\_cnt <=  ) OR (p\_arg3 > l\_cnt) THEN
    LET l\_retstr = NULL
ELSE
    LET l\_idx =
    WHILE tok.hasMoreTokens()
        LET l\_str = tok.nextToken()
        IF l\_idx = p\_arg3 THEN
            LET l\_retstr = l\_str
            EXIT WHILE
        END IF
        LET l\_idx = l\_idx +
    END WHILE
END IF
RETURN l\_retstr  

END FUNCTION

3）本人愚笨，试了几种方法，最后只能通过系统组合出来的条件，进行分割，再重组成自己所需的条件，用在临时表的查询上：

PRIVATE FUNCTION cnmq001_ins_tmp()
DEFINE l_sql STRING
DEFINE l_nmch006 LIKE nmch_t.nmch006
DEFINE l_nmcq006 LIKE nmcq_t.nmcq006
DEFINE l_nmck042 LIKE nmck_t.nmck042
DEFINE l_apde010 LIKE apde_t.apde010
DEFINE l_nmbb025 LIKE nmbb_t.nmbb025
DEFINE lr_tmp RECORD
nmbc002 LIKE nmbc_t.nmbc002,
nmbc002_desc LIKE type_t.chr500,
nmbc005 LIKE nmbc_t.nmbc005,
nmbcdocno LIKE nmbc_t.nmbcdocno,
nmbcseq LIKE nmbc_t.nmbcseq,
nmbc006 LIKE nmbc_t.nmbc006,
nmbc007 LIKE nmbc_t.nmbc007,
nmbc007_desc LIKE nmajl_t.nmajl003,
nmbcud001 LIKE nmbc_t.nmbcud001,
nmbc003 LIKE nmbc_t.nmbc003,
nmbc003_desc LIKE type_t.chr80,
inamt LIKE type_t.num20_6,
nmbc100 LIKE nmbc_t.nmbc100,
nmbc101 LIKE nmbc_t.nmbc101,
outamt LIKE type_t.num20_6,
nmbccrtid LIKE nmbc_t.nmbccrtid,
nmbccrtid_desc LIKE type_t.chr30,
nmbccrtdt LIKE nmbc_t.nmbccrtdt
END RECORD

#TEST by ljr
DEFINE l_wc STRING
DEFINE l_wc2 STRING
DEFINE l_wc3 STRING
DEFINE l_i INT
INITIALIZE l_wc3 TO NULL
CALL cl_replace_str(g_wc,"and","|") RETURNING l_wc
FOR l_i= TO split_itemcount(l_wc,"|")
LET l_wc2 = split(l_wc,"|",l_i)
IF l_wc2.getIndexOf()> AND l_wc2.getIndexOf()<= THEN LET l_wc3 = l_wc3,l_wc2," and " ELSE IF l_wc2.getIndexOf()> THEN
LET l_wc3 = l_wc3,l_wc2," and "
ELSE
IF l_wc2.getIndexOf()> THEN
LET l_wc3 = l_wc3,l_wc2," and "
ELSE
IF l_wc2.getIndexOf()> THEN
LET l_wc3 = l_wc3,l_wc2," and "
ELSE
IF l_wc2.getIndexOf()> AND l_wc2.getIndexOf()<= THEN
LET l_wc3 = l_wc3,l_wc2," and "
END IF
END IF
END IF
END IF
END IF
END FOR
IF NOT cl_null(l_wc3) THEN
LET l_wc3 = l_wc3.subString(,l_wc3.getLength()-)
ELSE
LET l_wc3 = " 1=1 "
END IF

#-#---begin---insert放入excute
LET l_sql = " INSERT INTO cnmq001_tmp VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,? )"
PREPARE cnmq001_ins_tmp2 FROM l_sql
#-#---end---

LET l_sql = " SELECT UNIQUE nmbc002,'',nmbc005,nmbcdocno,nmbcseq,nmbc006,nmbc007,'',nmbcud001, ",
" nmbc003,'',decode(nmbc006,'1',nmbc103,0) inamt,nmbc100,nmbc101,decode(nmbc006,'2',nmbc103,0) outamt,nmbccrtid,'',nmbccrtdt ",
" FROM nmbc_t ",
" WHERE nmbcent = ? AND nmbccomp = ? AND ",l_wc3
PREPARE cnmq001_ins_tmp FROM l_sql
DECLARE ins_curs CURSOR FOR cnmq001_ins_tmp

OPEN ins_curs USING g_enterprise,g_site
FOREACH ins_curs INTO lr_tmp.*

 SELECT ooag011 INTO lr\_tmp.nmbccrtid\_desc
  FROM ooag\_t WHERE ooagent=g\_enterprise
  AND ooag001 = lr\_tmp.nmbccrtid

 SELECT nmaal003 INTO lr\_tmp.nmbc002\_desc
   FROM nmaal\_t WHERE nmaalent=g\_enterprise
   AND nmaal001= lr\_tmp.nmbc002

 SELECT nmajl003 INTO lr\_tmp.nmbc007\_desc
   FROM nmajl\_t WHERE nmajlent=g\_enterprise
   AND nmajl001= lr\_tmp.nmbc007
   AND nmajl002 = g\_lang

  IF NOT cl\_null(lr\_tmp.nmbc003) THEN
     IF lr\_tmp.nmbc003='EMPL' THEN
        SELECT apca014 INTO lr\_tmp.nmbc003 FROM apca\_t
        WHERE apcaent=g\_enterprise
        AND apcadocno=lr\_tmp.nmbcdocno

        SELECT ooag011 INTO lr\_tmp.nmbc003\_desc FROM ooag\_t
        WHERE ooagent=g\_enterprise AND ooag001 = lr\_tmp.nmbc003
      ELSE
        LET lr\_tmp.nmbc003\_desc = s\_desc\_get\_trading\_partner\_full\_desc(lr\_tmp.nmbc003)
     END IF
   END IF
   #新增摘要
   IF cl\_null(lr\_tmp.nmbcud001) THEN
     #.包括其他收支单作业anmt310，客户收款单作业anmt540，抓取的是表nmbb\_t，抓取的栏位是nmbb025，
     #如果抓取不到则再去到下一项
      LET l\_nmbb025=''
      LET l\_apde010=''
      LET l\_nmck042=''
      LET l\_nmcq006=''
      LET l\_nmch006=''
      SELECT nmbb025 INTO l\_nmbb025 FROM nmbb\_t
       WHERE nmbbent=g\_enterprise
         AND nmbbdocno=lr\_tmp.nmbcdocno
         AND nmbbseq=lr\_tmp.nmbcseq
      IF cl\_null(l\_nmbb025) THEN
         #.应付单aapt330，aapt331，aapt310，aapt301，都是应付单，抓取的表都是apde\_t，
         #抓取的栏位都是apde010，如果抓取不到则再去到下一项
         SELECT apde010 INTO l\_apde010 FROM apde\_t
          WHERE apdeent=g\_enterprise
            AND apdedocno=lr\_tmp.nmbcdocno
            AND apdeseq=lr\_tmp.nmbcseq
         IF cl\_null(l\_apde010) THEN
            #汇款单anmt460,抓取表nmck\_t，抓取的栏位是nmck042，如果抓取不到则再去到下一项
            SELECT nmck042 INTO l\_nmck042 FROM nmck\_t
             WHERE nmckent=g\_enterprise
               AND nmckdocno=lr\_tmp.nmbcdocno
            IF cl\_null(l\_nmck042) THEN
              #.应收票据兑现单，抓取表nmcq\_t，抓取栏位是nmcq006，如果抓取不到则再去到下一项
              SELECT nmcq006 INTO l\_nmcq006 FROM nmcq\_t
               WHERE nmcqent=g\_enterprise
                 AND nmcqdocno=lr\_tmp.nmbcdocno
                 IF cl\_null(l\_nmcq006) THEN
                    #.应付票据兑现单，抓取表nmch\_t，抓取栏位是nmch006
                    SELECT nmch006 INTO l\_nmch006 FROM nmch\_t
                     WHERE nmchent=g\_enterprise
                       AND nmchdocno=lr\_tmp.nmbcdocno
                    LET lr\_tmp.nmbcud001=l\_nmch006
                 ELSE
                    LET lr\_tmp.nmbcud001=l\_nmcq006
                 END IF
            ELSE
               LET lr\_tmp.nmbcud001=l\_nmck042
            END IF
         ELSE
            LET lr\_tmp.nmbcud001=l\_apde010
         END IF 

      ELSE
        LET lr\_tmp.nmbcud001=l\_nmbb025
      END IF
   END IF        

   #-#
   #INSERT INTO cnmq001\_tmp VALUES(lr\_tmp.\*)
   EXECUTE cnmq001\_ins\_tmp2 USING lr\_tmp.\*

END FOREACH

END FUNCTION

分隔符

DEFINE tok       base.StringTokenizer

LET tok = base.StringTokenizer.create(l_cltname, '.') 
   WHILE tok.hasMoreTokens() 
      LET g_tc_img.tc_imgtype = tok.nextToken() 
   END WHILE