select 1 from dual where to_date('2008-10-30', 'yyyy-mm-dd') >=(select max(makedate)from lpedoritem where edortype in ('PD','ZP')
就是这条比较日期的sql,如果大于makedate,则返回1.但现在有一种情况就是max(makedate)为空,在max(date)为空的情况下需要sql返回1,但现在返回的是空
请教大侠们,该怎么写这条sql ,用case似乎可以实现,大家帮帮忙,比较急
就是这条比较日期的sql,如果大于makedate,则返回1.但现在有一种情况就是max(makedate)为空,在max(date)为空的情况下需要sql返回1,但现在返回的是空
请教大侠们,该怎么写这条sql ,用case似乎可以实现,大家帮帮忙,比较急
select case
when max(makedate)>to_date('2008-10-30', 'yyyy-mm-dd') then 1
when max(makedate) is null then 1
else 0 end
from lpedoritem where edortype in ('PD','ZP')
)
return number
IS
ret_num number(1):=0;
max_date date;
BEGIN select max(makedate)into max_date from lpedoritem where edortype in ('PD','ZP')
if trunc(in_date)>max_date then ret_num:=0;
else
ret_num:=1;
return ret_num;EXCEPTION
WHEN no_data_found THEN return 1;
WHEN others THEN return to_char(sqlcode);
END check_date ;然后调用的的时候
select check_date(sysdate) from dual
日期为空的,比较不了,我那条sql返回的空
when p.maxValue is null then 1
else 0
from (select max(makedate) maxValue from lpedoritem where edortype in ('PD','ZP') p
select 1 from dual where to_date('2008-10-30', 'yyyy-mm-dd') >=(select max(edorvalidate)from lpedoritem where edortype in('PD', 'ZP') and contno = '8026000000020268') and to_date('2008-10-30', 'yyyy-mm-dd') >=(select max(makedate) from lcinsureacctrace where contno = '8026000000020268' and busytype = 'JS')整个sql是这样的,需要比较两个日期,需求也一样,就是必须要同时大于那两个日期,日期为空就默认大于
select 1
from dual
where to_date('2008-10-30', 'yyyy-mm-dd')
>=(select nvl(max(makedate),to_date('1900-01-01', 'yyyy-mm-dd')) --当为null时,转为个小的日期
from lpedoritem where edortype in ('PD','ZP')
select case when to_date('2008-10-30', 'yyyy-mm-dd')>=p.maxValue then 1
when p.maxValue is null then 1
else 0
from
((select max(edorvalidate) maxValue from lpedoritem where edortype in('PD', 'ZP') and contno = '8026000000020268')
Union
(select max(makedate) maxValue from lcinsureacctrace where contno = '8026000000020268' and busytype = 'JS')) p
select 1
from dual
where to_date('2008-10-30', 'yyyy-mm-dd') >=
nvl((select max(edorvalidate)from lpedoritem where edortype in('PD', 'ZP') and contno = '8026000000020268'), '1800-01-01')
and to_date('2008-10-30', 'yyyy-mm-dd') >=
nvl((select max(makedate) from lcinsureacctrace where contno = '8026000000020268' and busytype = 'JS'), '1800-01-01')
select case
when
(select max(edorvalidate)from lpedoritem where edortype in('PD', 'ZP') and contno = '8026000000020268')<=to_date('2008-10-30', 'yyyy-mm-dd')
then 1
when (select max(edorvalidate)from lpedoritem where edortype in('PD', 'ZP') and contno = '8026000000020268') is null
then 1
when
(select max(makedate) from lcinsureacctrace where contno = '8026000000020268' and busytype = 'JS')<=to_date('2008-10-30', 'yyyy-mm-dd')
then 1
when (select max(makedate) from lcinsureacctrace where contno = '8026000000020268' and busytype = 'JS') is null
then 1
else 0 end
from dual;
感觉还是nvl效率要高点
感觉还是nvl效率要高点
难道开发者能写出比oracle函数更有效率的?