新做报表界面，想实现用时间进行查询，显示报表查询失败

tianshan80

报表用的是ACTION进行数据查询，SQL语句在PLSQL中运行正常，报错语句：
Caused by: com.justep.report.exception.ReportServerException: 执行sqlQueryAction时报错！SELECT KHMC,WLMC,HTBH,GLHTH,KZSL,WBDJ,KZYH,KZRQ,DQRQ,XYZLX,LCID,WBJE,YWLX,HTQDRQ,ZGSDS,CM,XHG,BXGS,BXF,JKZZS,SJF,DLF,GJF,BGF,XCF,SFWBJE,WKF,CIQSL FROM (SELECT  DISTINCT A.KHMC,A.WLMC,A.HTBH,A.GLHTH,A.KZSL,A.WBDJ,A.KZYH,A.KZRQ,A.DQRQ,A.XYZLX,A.LCID,A.WBJE,A.YWLX,(SELECT QDRQ FROM JKHTB F WHERE F.HTNO=A.HTNO) AS HTQDRQ,(SELECT SUM(ZGSDS) FROM SFHKB D WHERE D.HTNO=A.HTNO AND A.YWLX=D.YWLX) AS ZGSDS,(SELECT CM FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='商检费') AS CM,(SELECT XHG FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='商检费') AS XHG,(SELECT KHMC FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='保险费') AS BXGS,(SELECT JE FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='保险费') AS BXF,(SELECT JE FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='进口增值税') AS JKZZS,(SELECT JE FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='商检费') AS SJF,(SELECT JE FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='代理费') AS DLF, (SELECT JE FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='港建费') AS GJF, (SELECT JE FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='港口作业包干费') AS BGF, (SELECT JE FROM FYPJB B WHERE B.HTNO=A.HTNO AND B.DJMC='小船费') AS XCF, (SELECT SUM(WBJE) FROM SFHKB D WHERE D.HTNO=A.HTNO AND A.YWLX=D.YWLX ) AS SFWBJE,(SELECT WBTZJE FROM WKB C WHERE A.HTNO=C.HTNO AND A.YWLX=C.YWLX) AS WKF, (SELECT CIQSL FROM GKRKB E WHERE A.HTNO=E.HTNO AND YWLX='进口') AS CIQSL,A.DJR FROM XYZXXB A,SFHKB D,FYPJB B,WKB C,GKRKB E WHERE B.HTNO=D.HTNO AND C.YWLX=D.YWLX) where 1=1  and TRUNC(HTQDRQ) between to_date('null','YYYY-MM-DD') and to_date('null','YYYY-MM-DD')
        at com.justep.report.controller.DataController.a(Unknown Source)
        at com.justep.report.controller.DataController.a(Unknown Source)
        ... 20 more

按照论坛上的方法都试了，不加
if(!"".equals(KSSJ)&&!"".equals(JSSJ))
{
oracle += " and TRUNC(HTQDRQ) between to_date('"+KSSJ+"','YYYY-MM-DD') and to_date('"+JSSJ+"','YYYY-MM-DD')";
}
的时候正常。
JS里获取时间语句如下：
var data1 = justep.xbl('data1');
//reportData
    var reportDate = justep.xbl('ds1');
//得到开始时间
    if(data1.getValue('KSSJ').trim() != ''){
            var d1 = data1.getValue('KSSJ');
            d1 = d1.substr(0, 22);
            d1 = d1.replace("T", " ");
            reportDate.setStringVar('KSSJ', d1);
    }
//得到结束时间
    if(data1.getValue('JSSJ').trim() != ''){
            var d2 = data1.getValue('JSSJ');
            d2 = d2.substr(0, 22);
            d2 = d2.replace("T", " ");
            reportDate.setStringVar('JSSJ', d2);
    }
//刷新报表
    justep.xbl('report1').refresh();

求解决，求远程。

yinlun

传递值出了问题
var d1 = data1.getValue('KSSJ');和var d2 = data1.getValue('JSSJ');
都能正确的到值吗？

stephenewong

同意楼上的，很明显，TRUNC(HTQDRQ) between to_date('null','YYYY-MM-DD') and to_date('null','YYYY-MM-DD') 提示你数据库对空值日期进行操作。建议调试js中日期取得的值。

tianshan80

我也知道是获取值没获取到的原因，但是这个语句应该是没问题的啊，是直接用正确的语句改的，语法没问题啊，我就是看不出来为什么没获取到。。。。。。

zhaixin

如果没有选择日期，也就没有执行 reportDate.setStringVar('JSSJ', d2); 那么JSSJ'参数没有赋值
if(!"".equals(KSSJ)&&!"".equals(JSSJ))
{
oracle += " and TRUNC(HTQDRQ) between to_date('"+KSSJ+"','YYYY-MM-DD') and to_date('"+JSSJ+"','YYYY-MM-DD')";
}
这段代码应该判断一下，传过来的参数是否为空

tianshan80

如果值都为空，允许插叙出所有值，应该怎么判断啊？

tianshan80

有没有人帮忙看一下啊？我现在直接把两个值填上固定值也不行啊
if(!"".equals(KSSJ)&&!"".equals(JSSJ)){
                        oracle += " and HTQDRQ between to_date('"+KSSJ+"','YYYY-MM-DD') and to_date('"+JSSJ+"','YYYY-MM-DD')";
                        }
        else{
                oracle += " and HTQDRQ between to_date('2012-01-01','YYYY-MM-DD') and to_date('2013-12-26','YYYY-MM-DD')";
                }

tianshan80

如果没有选择日期，也就没有执行 reportDate.setStringVar('JSSJ', d2); 那么JSSJ'参数没有赋值
if(!"".equals(KSSJ)&&!"".equals(JSSJ))
{
oracle += " and TRUNC(HTQDRQ) between to_date('"+KSSJ+"','YYYY-MM-DD') and to_date('"+JSSJ+"','YYYY-MM-DD')";
}
这段代码应该判断一下，传过来的参数是否为空

这个没人帮忙吗？怎么解决给个话啊。。。zhaixin老大你说的详细点呗？

song_ning_ning

你好楼主，该问题需要远程，请在你的个人信息中完善QQ和联系电话，我们会尽快QQ远程。

tianshan80

资料更新了，可以远程了