where条件 - 我的帆软

where条件

发货日期和签收日期做了互斥，用户只能选择一个日期查询，

但是语句怎么改？之前只写了一个CALDAY的WHERE条件，

如果想再增加一个DATE0，应该写IF条件吗？

主要我是之前的语句有大量的CASE WHEN，似乎不太好改

FineReport 学习帆软ing 发布于 2023-6-13 12:32

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共1回答

最佳回答

CD20160914Lv8专家互助
发布于2023-6-13 12:37（编辑于 2023-6-13 12:47）

不是在你这里改。你在最后where 后面改

---------------------------------

select 你原来的语句放进来 from 表名称

where 1=1

${if(len(CALDAY)=0&&len(DATE0)=0,"",if(len(CALDAY)=0,"and 签收日期='"+ DATE0 + "'","and 发货日期='"+ CALDAY + "'"))}

/*增加一个判断，如果两个日期都不选择就为空*/

-------------写在上面的语句改成这样

select

名称,

${if(len(CALDAY)=0&&len(DATE0)=0,"",if(len(CALDAY)=0,"substr(to_char('"+DATE0+"','yyyymmdd'),1,6)","substr(to_char('"+CALDAY+"','yyyymmdd'),1,6)"))} as 判断

from 表名称

where 1=1

举报收起评论(3)

学习帆软ing(提问者) SELECT A.ZICZA0004,SUM(A.ZIFDTQ001), SUM(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAV010 END) AS XS_DY, SUM(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFDTV001 END) AS XS_OMS, SUM(CASE WHEN CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAV010 END) AS XS_DN, SUM(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(ADD_MONTHS(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),-1),\'YYYYMMDD\'),1,6) AND CALDAY <= CASE WHEN TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') = TO_CHAR(LAST_DAY(\'${CALDAY}\'),\'YYYYMMDD\') THEN TO_CHAR(LAST_DAY(TO_CHAR(ADD_MONTHS(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),-1),\'YYYYMMDD\')),\'YYYYMMDD\') ELSE TO_CHAR(ADD_MONTHS(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),-1),\'YYYYMMDD\') END THEN A.ZIFXAV010 END) AS XS_SY, SUM(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(ADD_YEARS(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),-1),\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(ADD_YEARS(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),-1),\'YYYYMMDD\') THEN A.ZIFXAV010 END) AS XS_QNDY, SUM(CASE WHEN CALYEAR = SUBSTR(TO_CHAR(ADD_YEARS(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),-1),\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(ADD_YEARS(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),-1),\'YYYYMMDD\') THEN A.ZIFXAV010 END) AS XS_QNDN, SUM(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAV018 END) AS MLE_DY, SUM(CASE WHEN CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAV018 END) AS MLE_DN, SUM(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAV015 END) AS SR_DY, SUM(CASE WHEN CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAV015 END) AS SR_DN, SUM(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAC017 END) AS CB_DY, SUM(CASE WHEN CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN A.ZIFXAC017 END) AS CB_DN, SUM(CASE WHEN C.PF = \'Y\' AND CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN (ZIFXAV015 - A.ZIFXAC017 - (ZIFXAV015 - A.ZIFXAC017) * 0.17 * 0.1 - A.ZIFXAV010 * IFNULL(C.KD,0) / 1.06 - ABS(A.ZIFXAV010 * IFNULL(C.DYF,0) / 1.06) ) WHEN C.PF = \'N\' AND ZICZA0029 <> \'\' AND CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN (ZIFXAV015 - A.ZIFXAC017 - (ZIFXAV015 - A.ZIFXAC017) * 0.17 * 0.1 - ZIFXAV015 * 0.05 - ZIFXAV015 * 0.05 * 0.1 - A.ZIFXAV010 * IFNULL(C.KD,0) / 1.06 - ABS(A.ZIFXAV010 * IFNULL(C.DYF,0) / 1.06) ) WHEN C.PF = \'N\' AND ZICZA0029 = \'\' AND CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN (ZIFXAV015 - A.ZIFXAC017 - (ZIFXAV015 - A.ZIFXAC017) * 0.17 * 0.1 - A.ZIFXAV010 * IFNULL(C.KD,0) / 1.06 - ABS(A.ZIFXAV010 * IFNULL(C.DYF,0) / 1.06) ) END) AS BHSMLE_DY, SUM(CASE WHEN C.PF = \'Y\' AND CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN (ZIFXAV015 - A.ZIFXAC017 - (ZIFXAV015 - A.ZIFXAC017) * 0.17 * 0.1 - A.ZIFXAV010 * IFNULL(C.KD,0) / 1.06 - ABS(A.ZIFXAV010 * IFNULL(C.DYF,0) / 1.06) ) WHEN C.PF = \'N\' AND ZICZA0029 <> \'\' AND CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN (ZIFXAV015 - A.ZIFXAC017 - (ZIFXAV015 - A.ZIFXAC017) * 0.17 * 0.1 - ZIFXAV015 * 0.05 - ZIFXAV015 * 0.05 * 0.1 - A.ZIFXAV010 * IFNULL(C.KD,0) / 1.06 - ABS(A.ZIFXAV010 * IFNULL(C.DYF,0) / 1.06) ) WHEN C.PF = \'N\' AND ZICZA0029 = \'\' AND CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') THEN (ZIFXAV015 - A.ZIFXAC017 - (ZIFXAV015 - A.ZIFXAC017) * 0.17 * 0.1 - A.ZIFXAV010 * IFNULL(C.KD,0) / 1.06 - ABS(A.ZIFXAV010 * IFNULL(C.DYF,0) / 1.06) ) END) AS BHSMLE_DN, COUNT(DISTINCT(CASE WHEN CALMONTH = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,6) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') AND ZIFXAV010>0 THEN A.ZICZG0005 END)) AS DDL_DY, COUNT(DISTINCT(CASE WHEN CALYEAR = SUBSTR(TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\'),1,4) AND CALDAY <= TO_CHAR(\'${CALDAY}\',\'YYYYMMDD\') AND ZIFXAV010>0 THEN A.ZICZG0005 END)) AS DDL_DN FROM \"_SYS_BIC\".\"CB.AL.YW/ZAL_SALES_DETAIL2\" A --INNER JOIN \"_SYS_BIC\".\"CB.BL.MD.MTABLE/PCUSTOMER\" B ON A.CUSTOMER = B.CUSTOMER LEFT JOIN \"_SYS_BIC\".DSSF_SS C ON A.ZICZA0004 = C.DL AND A.PAYER = C.DP WHERE 1=1 AND A.ZICXA0004 = \'20\' --AND A.ZICXA0004 <> \'10\' AND A.ZICZG0004=\'${EDBOMS}\' AND A.PAYER LIKE (\'S7%\') --AND B.ACCNT_GRP = \'Z001\' ${GET_PAYER} ${GET_ZICZA0004} GROUP BY A.ZICZA0004 ORDER BY A.ZICZA0004

2023-06-13 13:41