帆软finereport笔记：SQL中if的使用

1 ${if(len(aae002)==0,"","and SUBSTR(T.AAE002, 0, 4)='"+aae002+"'")}
2 ${if(len(aab301)==0,"","and T.AAB301='"+aab301+"'")}
3 ${if(len(pch)==0,"","and Y.AAE244='"+pch+"'")}

SELECT ROWNUM,
       YEARNAME,
       TABTIME,
       AABB301NAME,
       PCH,
       VILNAME,
       AAC003,
       AAC002,
       (CASE
         WHEN AAC060 = '0' THEN
          '嘿嘿嘿'
         WHEN AAC084 = '1' THEN
          '哈哈哈'
         WHEN AAC008 = '1' THEN
          '在在在'
       END) AS PERSTATUS,
       PRONAME,
       TOTALPAY,
       ALPAY,
       NOWPAY,
       (TOTALPAY - ALPAY - NOWPAY) AS BALPAY
  FROM (SELECT TO_CHAR(SYSDATE, 'yyyy') || '年度' AS YEARNAME,
               TO_CHAR(SYSDATE, 'yyyy') || '年' || TO_CHAR(SYSDATE, 'MM') || '月' ||
               TO_CHAR(SYSDATE, 'dd') || '日' AS TABTIME,
               (SELECT AAA146 FROM AA26 WHERE AA26.AAB301 = T.AAB301) AS AABB301NAME,
               --SUBSTR(PCH, 0, 6) AS PCH, --批次号
               Y.AAE244 AS PCH, --批次号
               A.AAC084,
               A.AAC060, 
               B.AAC008,
               (SELECT DISTINCT T.AAF031
                  FROM AF02@ZJHZRS_OTH T
                 WHERE T.AAF013 = D.AAF013) ||
               (SELECT DISTINCT T.AAF040
                  FROM AF02@ZJHZRS_OTH T
                 WHERE T.AAF030 = D.AAF030) AS VILNAME,
               C.AAC003,
               C.AAC002,
               D.BAB107 AS PRONAME,
               (SELECT BAE366
                  FROM BA35
                 WHERE BA35.AAB301 = T.AAB301
                   AND TO_CHAR(SYSDATE, 'yyyyMM') BETWEEN BA35.AAE041 AND
                       BA35.AAE042
                   AND C.BAB110 BETWEEN BA35.AAE030 AND BA35.AAE031) AS TOTALPAY, --总金额
               (SELECT NVL(SUM(BAC046), 0)
                  FROM ACD8
                 WHERE AAE117 = '1'
                   AND BAA121 = '1111'
                   AND ACD8.AAC001 = T.AAC001) AS ALPAY, --已支付金额
               T.BAC046 AS NOWPAY, --现支付金额
               '0' AS BALPAY --余额
          FROM BD34            T,
               AD34            Y,
               AC01            A,
               AC02            B,
               BC15@ZJHZRS_OTH C,
               BB10@ZJHZRS_OTH D
         WHERE T.AAZ031 = Y.AAZ031
           AND D.BAZ216 = C.BAZ216
           AND A.AAC002 = C.AAC002
           AND T.AAC001 = A.AAC001
           AND A.AAC001 = B.AAC001
           AND B.AAE100 = '1'
           AND B.AAE140 = '110'
           AND T.BAA121 = '1111' 
           ${if(len(aae002)==0,"","and SUBSTR(T.AAE002, 0, 4)='"+aae002+"'")}
           ${if(len(aab301)==0,"","and T.AAB301='"+aab301+"'")}
           ${if(len(pch)==0,"","and Y.AAE244='"+pch+"'")}
         ORDER BY T.AAB301, D.AAF030, D.BAZ216, A.AAC002, Y.AAE244)