orcale语法问题

SELECT "c".TASK_LEVEL,
"c".NUMBER2,
"c".SUBORDINATE_SYSTEM,
"c".CONTENT_TASK,
"c".PLANNED_DELIVERABLES,
"c".THOSE_RESPONSIBLE,
"c".COMPLETION_DATE,
"c".REMAINING_DAYS,
"c".ACTUAL_COMPLETION_DATE,
"c".STATE,
"c".LAST_WEEK_STATE,
"c".THIS_WEEK_STATE,
"c".DEPARTMENT,
"c".TASK_PERSON,
"c".TASK_CREATE_DAY,
"c".TASKS_REPORTING_DAY,
"c".TASK_REPLY_ADDED,
"c".FYMONTH,
"c".FDATE,
"c".WEEK
FROM(SELECT
"a".TASK_LEVEL,
"a".NUMBER2,
"a".SUBORDINATE_SYSTEM,
"a".CONTENT_TASK,
"a".PLANNED_DELIVERABLES,
"a".THOSE_RESPONSIBLE,
"a".COMPLETION_DATE,
"a".REMAINING_DAYS,
"a".ACTUAL_COMPLETION_DATE,
"a".STATE,
"a".LAST_WEEK_STATE,
"a".THIS_WEEK_STATE,
"a".DEPARTMENT,
"a".TASK_PERSON,
"a".TASK_CREATE_DAY,
"a".TASKS_REPORTING_DAY,
"a".TASK_REPLY_ADDED,
"b".FYMONTH,
"b".FDATE,
CASE SUBSTR("b".FMWEEK,7,1)
WHEN '1' THEN '第一周'
WHEN '2' THEN '第二周'
WHEN '3' THEN '第三周'
WHEN '4' THEN '第四周'
WHEN '5' THEN '第五周'
ELSE '第六周' END WEEK
FROM
NEW_REPORT.TASK_MAINTENANCE "a" ,
NEW_REPORT.MDM_TIME "b"
WHERE
"a".TASKS_REPORTING_DAY = "b".FDATE)"c"
where 1=1
${if(len(任务级别)==0,"","and 'c'.task_level in ('"+任务级别+"')")}
${if(len(责任人)==0,""," and 'c'.those_responsible like '%"+ 责任人 +"%'")}
${if(len(任务内容)==0,""," and 'c'.content_task like '%"+ 任务内容 +"%'")}
${if(len(计划完成日)==0, ""," and TO_CHAR('c'.COMPLETION_DATE, 'YYYY-MM-DD') = '"+计划完成日+ "'")}
${if(len(任务状态)==0,"","and 'c'.state in ('"+任务状态+"')")}
${if(len(week)==0,"","and 'c'.WEEK in('"+week+"')")}
order by "c".number2

正常预览没有问题,传参就报错了

FineReport 用户kWEye3931 发布于 2020-6-9 17:13 （编辑于 2020-6-9 17:22）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

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

共5回答

最佳回答

cherry团子Lv6中级互助
发布于2020-6-9 17:52

微信解决:

把别名的双引号去掉，不需要，后面参数可以运行正常

飞段Lv6初级互助
发布于2020-6-9 17:16

日期那边出问题了，${if(len(计划完成日)==0, ""," and TO_CHAR('c'.COMPLETION_DATE, 'YYYY-MM-DD') = to_char（'"+计划完成日+ "'"，“'YYYY-MM-DD'”）)},具体的小括号我没注意看，你大概看一下

snrtuemcLv8专家互助
发布于2020-6-9 17:17（编辑于 2020-6-9 17:22）

你看看in语句的几个，控件设置有没有对，按照这个设置

${if(len(计划完成日)==0, ""," and TO_CHAR('c'.COMPLETION_DATE, 'YYYY-MM-DD') = '"+计划完成日+ "'")}

这个里面应该是，注意标黑字体

${if(len(计划完成日)==0, ""," and TO_CHAR('c'.COMPLETION_DATE, 'yyyy-MM-dd') = '"+计划完成日+ "'")}

举报收起评论(1)

用户kWEye3931(提问者) 好像不是这个问题,因为我不是在浏览器预览的所以还不需要设置控件

2020-06-09 17:23

LSQ000Lv5中级互助
发布于2020-6-9 17:33（编辑于 2020-6-9 17:38）

直接设置默认参数然后预览页面，然后看日志sql输出了什么，把sql拿出来运行一下先定位一下错误

weisonzhouLv5见习互助
发布于2020-6-9 17:33（编辑于 2020-6-9 17:35）

这种传参写法有问题吧？

where 1=1
${if(len(任务级别)==0,"","and 'c'.task_level in ('"+任务级别+"')")}
${if(len(责任人)==0,""," and 'c'.those_responsible like '%"+ 责任人 +"%'")}
${if(len(任务内容)==0,""," and 'c'.content_task like '%"+ 任务内容 +"%'")}
${if(len(计划完成日)==0, ""," and TO_CHAR('c'.COMPLETION_DATE, 'YYYY-MM-DD') = '"+计划完成日+ "'")}
${if(len(任务状态)==0,"","and 'c'.state in ('"+任务状态+"')")}
${if(len(week)==0,"","and 'c'.WEEK in('"+week+"')")}
order by "c".number2

据我所知，finereport是java开发的，其传参方式和java的一致，应该是${参数名}，应该改成以下这种方式试试：

where
if(len(${任务级别})==0,"1=1","'c'.task_level in ('"+${任务级别}+"')")

and
if(len(${责任人})==0,"1=1"," 'c'.those_responsible like '%"+ ${责任人}+"%'")

and
if(len(${任务内容})==0,"1=1"," 'c'.content_task like '%"+ ${任务内容} +"%'")and

and
if(len(${计划完成日})==0, "1=1"," TO_CHAR('c'.COMPLETION_DATE, 'YYYY-MM-DD') = '"+${计划完成日}+ "'")
and

if(len(${任务状态})==0,"1 =1"," 'c'.state in ('"+${任务状态}+"')")

and
if(len(${week})==0,"1=1"," 'c'.WEEK in('"+${week}+"')")
order by "c".number2