NVL2(&sDate, fdh.GETDATE )前面也用个
to_date(NVL2(&sDate, fdh.GETDATE ),'yyyy-mm-dd')
to_date(NVL2(&sDate, fdh.GETDATE ),'yyyy-mm-dd')
解决方案 »
- 紧急求救:oracle9i导出/导入问题
- 逐级往上汇总
- 在导入过程中出现“SQL*Loader-404: 列 在 YQ_CS 的 INTO TABLE 块中出现的次数不止一次。”是为什么?
- 一个基本的在存储过程中使用游标的例子,不知为何出错,请大家帮忙!
- 哪位牛人给帮我把这个sqlserver的触发器翻译成oracle的?
- ASP中OracleInProcServer.XOraSession,OraSession怎么总是丢失?
- 请教:Oracle中AutoID表有什么用!
- 高分请教-向数据库一次插入批量数据
- 为什么不能向日志组增加日志成员?--使用Oracle817
- grant connect,resource to user 究竟包括那些权限,谢谢!20分
- 求一条sql语句
- 有点难度:怎样使最新录入的数据显示在最前面
fdh.getdate, fdh.pledge, fdh.getcost, fdh.fixamt, fdh.rcost,
fdh.depcost, fdh.depyear, fdh.depmon, fdh.yeardep, fdh.deptot,
fdh.getcost + fdh.fixamt - deptot, fdh.input
FROM fams_document_heads fdh, fa_cate fc, fams_department fd
WHERE fc.cate_no = fdh.mainid
AND fdh.ndepid = fd.department_no /*and 1=1*/
AND NVL (TO_DATE (&sdate, 'yyyy-mm-dd'),
fdh.getdate BETWEEN TO_DATE (&sdate, 'yyyy-mm-dd')
AND TO_DATE (&fdate, 'yyyy-mm-dd'),
1 = 1
)
fdh.getdate, fdh.pledge, fdh.getcost, fdh.fixamt, fdh.rcost,
fdh.depcost, fdh.depyear, fdh.depmon, fdh.yeardep, fdh.deptot,
fdh.getcost + fdh.fixamt - deptot, fdh.input
FROM fams_document_heads fdh, fa_cate fc, fams_department fd
WHERE fc.cate_no = fdh.mainid
AND fdh.ndepid = fd.department_no /*and 1=1*/
AND NVL2(TO_DATE (&sdate, 'yyyy-mm-dd'),
fdh.getdate BETWEEN TO_DATE (&sdate, 'yyyy-mm-dd')
AND TO_DATE (&fdate, 'yyyy-mm-dd'),
1 = 1
)
似乎可以,没试过,你试试吧
NVL2(&sDate, fdh.GETDATE between to_date(&sDate, 'yyyy-mm-dd') and to_date(&fDate, 'yyyy-mm-dd') , 1=1 )函数返回1=1,它是一个布尔表达式,所以应该没问题。
fdh.getdate, fdh.pledge, fdh.getcost, fdh.fixamt, fdh.rcost,
fdh.depcost, fdh.depyear, fdh.depmon, fdh.yeardep, fdh.deptot,
fdh.getcost + fdh.fixamt - deptot, fdh.input
FROM fams_document_heads fdh, fa_cate fc, fams_department fd
WHERE fc.cate_no = fdh.mainid
AND fdh.ndepid = fd.department_no /*and 1=1*/
AND NVL2(TO_DATE (&sdate, 'yyyy-mm-dd'),
fdh.getdate BETWEEN TO_DATE (&sdate, 'yyyy-mm-dd')
AND TO_DATE (&fdate, 'yyyy-mm-dd'),
1 = 1
)
我觉得这样写也没有问题,可是oracle 就是报错误:
invalid number of arguments
where nvl2(col1,'col1','1')=1;这样就没错了,我的col1是字符类型的,如果是日期类型,要自己使用to_date(col_name,'yyyy-mm-dd')进行处理。
fdh.getdate, fdh.pledge, fdh.getcost, fdh.fixamt, fdh.rcost,
fdh.depcost, fdh.depyear, fdh.depmon, fdh.yeardep, fdh.deptot,
fdh.getcost + fdh.fixamt - fdh.deptot, fdh.input
FROM fams_document_heads fdh, fa_cate fc, fams_department fd
WHERE fc.cate_no = fdh.mainid
AND fdh.ndepid = fd.department_no
AND fdh.getdate >= NVL (:p_fromdate, fdh.getdate)
AND fdh.getdate <= NVL (:p_todate, fdh.getdate)
AND fc.cate_no >= NVL (:p_fromcate, fc.cate_no)
AND fc.cate_no <= NVL (:p_tocate, fc.cate_no)
AND fd.department_no >= NVL (:p_fromdep, fd.department_no)
AND fd.department_no <= NVL (:p_todep, fd.department_no)