大家有见过把NVL() , DECODE() 函数写到WHERE 后面吗?

[sylmoon]

这样写可以吗
我写了总是在提示"没有结束标记,语句不正确"

SELECT fd.department_name, fc.cate_name, fdh.newid, fdh.nfixname, fdh.qty,
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
NVL2(&sDate, and fdh.GETDATE between to_date(sDate, 'yyyy-mm-dd') and to_date(&fDate, 'yyyy-mm-dd') , and 1=1 )


我刚改了一下:

SELECT fd.department_name, fc.cate_name, fdh.newid, fdh.nfixname, fdh.qty,
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(&sDate, fdh.GETDATE between to_date(sDate, 'yyyy-mm-dd') and to_date(&fDate, 'yyyy-mm-dd') , 1=1 )

还是报错:
invalid number of arguments

[Renkey]

NVL2(&sDate, fdh.GETDATE )前面也用个
to_date(NVL2(&sDate, fdh.GETDATE ),'yyyy-mm-dd')

[sylmoon]

改成了这样了,还不行

SELECT fd.department_name, fc.cate_name, fdh.newid, fdh.nfixname, fdh.qty,
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
)

[anglequhongmei]

nvl只有两个参数，nvl2才有3个参数。你写的and NVL2(&sDate, fdh.GETDATE between to_date(sDate, 'yyyy-mm-dd') and to_date(&fDate, 'yyyy-mm-dd') , 1=1 ) ，返回的结果事boolean变量吗？

[zealot_zk]

SELECT fd.department_name, fc.cate_name, fdh.newid, fdh.nfixname, fdh.qty,
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
)
似乎可以，没试过，你试试吧

[zealot_zk]

to:anglequhongmei(蛐蛐)
NVL2(&sDate, fdh.GETDATE between to_date(&sDate, 'yyyy-mm-dd') and to_date(&fDate, 'yyyy-mm-dd') , 1=1 )函数返回1=1,它是一个布尔表达式，所以应该没问题。

[sylmoon]

SELECT fd.department_name, fc.cate_name, fdh.newid, fdh.nfixname, fdh.qty,
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

[zealot_zk]

我试了一下知道为什么了，这是因为，nvl2(a,b,c)要求三个参数的类型一样，另外，如果想使用这样的nvl2(a,b,c)函数还要注意，条件一定要写在函数外面，不然会报“不可用的关系运算符”我写了一个简单的例子，你参考改一下吧，呵呵

select count(*) from mytest_tab
where nvl2(col1,'col1','1')=1;

这样就没错了，我的col1是字符类型的，如果是日期类型，要自己使用to_date(col_name,'yyyy-mm-dd')进行处理。

[sylmoon]

参数可不可以写表达式呢,如果不准写表达式那还式不可以

[zealot_zk]

nvl2()的参数可以为表达式,报错的原因是where子句的问题,where只能是逻辑表达式,而nvl2()不是逻辑表达式。

[sylmoon]

写好了:

SELECT fd.department_no, fd.department_name, fc.cate_no, fc.cate_name, fdh.newid, fdh.nfixname, fdh.qty,
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)