我这个哪里有问题?预览时选择参数不生效

${IF(AND(len(LOCATION)==0 ,len(EQPGROUP)==0 ,len(EQPID)==0),"SELECT EQPID, STATE, STIME, ETIME, DETAIL, TXNDATE, MOTHEREQP, 

 ROUND(24*(TO_DATE(ETIME,'YYYYMMDD HH24MISS')-TO_DATE(STIME,'YYYYMMDD HH24MISS')),2) PERIOD  FROM TMP_TB_TOOL_STATE  WHERE 1=1 AND TXNDATE >= '${fromDate}' 

AND TXNDATE < '${toDate}'  ORDER BY EQPID DESC, STIME",

IF(AND(len(LOCATION)==0 ,len(EQPGROUP)==0 ,len(EQPID)>0),"SELECT EQPID, STATE, STIME, ETIME, DETAIL, TXNDATE, MOTHEREQP, 

                            ROUND(24*(TO_DATE(ETIME,'YYYYMMDD HH24MISS')-TO_DATE(STIME,'YYYYMMDD HH24MISS')),2) PERIOD 

                            FROM TMP_TB_TOOL_STATE 

                            WHERE 1=1 and eqpid in ('${EQPID}')

                            AND TXNDATE >= '${fromDate}' 

                            AND TXNDATE < '${toDate}' 

                            ORDER BY EQPID DESC, STIME",

IF(AND(len(LOCATION)==0 ,len(EQPGROUP)>0 ,len(EQPID)==0),"SELECT SYSDATE FROM DUAL",

IF(AND(len(LOCATION)>0 ,len(EQPGROUP)==0 ,len(EQPID)==0),"SELECT SYSDATE FROM DUAL",

IF(AND(len(LOCATION)==0 ,len(EQPGROUP)>0 ,len(EQPID)>0),"SELECT SYSDATE FROM DUAL",

IF(AND(len(LOCATION)>0 ,len(EQPGROUP)==0 ,len(EQPID)>0),"SELECT SYSDATE FROM DUAL",

IF(AND(len(LOCATION)>0 ,len(EQPGROUP)>0 ,len(EQPID)==0),"SELECT SYSDATE FROM DUAL",

IF(AND(len(LOCATION)>0 ,len(EQPGROUP)>0 ,len(EQPID)>0),"SELECT SYSDATE FROM DUAL",""))))))))}


宝宝的宝宝的宝宝 发布于 2020-11-18 15:13
1min目标场景问卷 立即参与
回答问题
悬赏:3 F币 + 添加悬赏
提示:增加悬赏、完善问题、追问等操作,可使您的问题被置顶,并向所有关注者发送通知
共3回答
最佳回答
0
axingLv6专家互助
发布于2020-11-18 15:18

非常不建议这么写,你写的麻烦,维护起来更麻烦。

建议每个查询写一个sql。

然后通过关联数据集写条件判断执行哪个

关联数据集-https://help.fanruan.com/finereport/doc-view-125.html


最佳回答
0
snrtuemcLv8专家互助
发布于2020-11-18 15:18(编辑于 2020-11-18 15:21)
${IF(AND(len(LOCATION)==0 ,len(EQPGROUP)==0 ,len(EQPID)==0),"SELECT EQPID, STATE, STIME, ETIME, DETAIL, TXNDATE, MOTHEREQP, 
 ROUND(24*(TO_DATE(ETIME,'YYYYMMDD HH24MISS')-TO_DATE(STIME,'YYYYMMDD HH24MISS')),2) PERIOD  FROM TMP_TB_TOOL_STATE  WHERE 1=1 AND TXNDATE >= '"+fromDate+"' 
AND TXNDATE < '"+toDate+"'  ORDER BY EQPID DESC, STIME",
IF(AND(len(LOCATION)==0 ,len(EQPGROUP)==0 ,len(EQPID)>0),"SELECT EQPID, STATE, STIME, ETIME, DETAIL, TXNDATE, MOTHEREQP, 
                            ROUND(24*(TO_DATE(ETIME,'YYYYMMDD HH24MISS')-TO_DATE(STIME,'YYYYMMDD HH24MISS')),2) PERIOD 
                            FROM TMP_TB_TOOL_STATE 
                            WHERE 1=1 and eqpid in ('"+EQPID+"')
                            AND TXNDATE >= '"+fromDate+"' 
                            AND TXNDATE < '"+toDate+"' 
                            ORDER BY EQPID DESC, STIME",
IF(AND(len(LOCATION)==0 ,len(EQPGROUP)>0 ,len(EQPID)==0),"SELECT SYSDATE FROM DUAL",
IF(AND(len(LOCATION)>0 ,len(EQPGROUP)==0 ,len(EQPID)==0),"SELECT SYSDATE FROM DUAL",
IF(AND(len(LOCATION)==0 ,len(EQPGROUP)>0 ,len(EQPID)>0),"SELECT SYSDATE FROM DUAL",
IF(AND(len(LOCATION)>0 ,len(EQPGROUP)==0 ,len(EQPID)>0),"SELECT SYSDATE FROM DUAL",
IF(AND(len(LOCATION)>0 ,len(EQPGROUP)>0 ,len(EQPID)==0),"SELECT SYSDATE FROM DUAL",
IF(AND(len(LOCATION)>0 ,len(EQPGROUP)>0 ,len(EQPID)>0),"SELECT SYSDATE FROM DUAL",""))))))))}

image.png



最佳回答
0
朝赟丶Lv6初级互助
发布于2020-11-18 15:28
${IF(AND(len(LOCATION)==0 ,len(EQPGROUP)==0) ,
SELECT EQPID,
       STATE,
       STIME,
       ETIME,
       DETAIL,
       TXNDATE,
       MOTHEREQP,
       ROUND(24 * (TO_DATE(ETIME, 'YYYYMMDD HH24MISS') -
             TO_DATE(STIME, 'YYYYMMDD HH24MISS')),
             2) PERIOD
  FROM TMP_TB_TOOL_STATE
 WHERE 1 = 1 
 ${if(len(EQPID) == 0, "", " and eqpid in('" + EQPID + "')") }
   AND TXNDATE >= '${fromDate}'
   AND TXNDATE < '${toDate}'
 ORDER BY EQPID DESC, STIME

前两个建议合并,其余的建议做关联数据集吧

  • 4关注人数
  • 439浏览人数
  • 最后回答于:2020-11-18 15:28
    请选择关闭问题的原因
    确定 取消
    返回顶部