create or replace FUNCTION lj_getsupply(FMaterialID varchar2, fdate1 date, fdate2 date) return varchar2 is re varchar2(100); begin for c1 in (select distinct e.fname_l2 fname from T_IM_PurInWarehsBill a inner join T_IM_PurInWarehsEntry b on a.fid = b.FParentID inner join T_SCM_BizType c on a.FBizTypeID = c.fid inner join T_SCM_TransactionType d on a.FTransactionTypeID = d.FID inner join T_BD_Supplier e on a.FSupplierID = e.fid where a.FBizDate >= fdate1 and a.FBizDate <= fdate2 and c.FName_L2 in ('普通采购', '普通采购退货') and d.FName_L2 in ('普通采购/委外入库', '普通采购/委外退货', '普通采购收货检验合格入库') and b.FMaterialID = FMaterialID) loop re := re || c1.fname; end loop; RETURN(regexp_replace(re, substr(re, 1, 1), '', 1, 1)); end;select lj_getsupply(FMaterialID,'2014-01-01','2014-12-31') as fsupply from T_IM_PurInWarehsEntry
select lj_getsupply(FMaterialID,to_date('2014-01-01','yyyy-mm-dd'),to_date('2014-12-31','yyyy-mm-dd') as fsupply from T_IM_PurInWarehsEntry
select lj_getsupply(FMaterialID,to_date('2014-01-01','yyyy-mm-dd'),to_date('2014-12-31','yyyy-mm-dd') as fsupply from T_IM_PurInWarehsEntry 提示 missing right parenthesis
numeric or value error: character string buffer too small
create or replace FUNCTION lj_getsupply(FMaterialID varchar2, fdate1 date, fdate2 date) return varchar2 is re varchar2(1500); begin for c1 in (select distinct e.fname_l2 fname
from T_IM_PurInWarehsBill a inner join T_IM_PurInWarehsEntry b on a.fid = b.FParentID inner join T_SCM_BizType c on a.FBizTypeID = c.fid inner join T_SCM_TransactionType d on a.FTransactionTypeID = d.FID inner join T_BD_Supplier e on a.FSupplierID = e.fid where a.FBizDate >= fdate1 and a.FBizDate <= fdate2 and c.FName_L2 in ('普通采购', '普通采购退货') and d.FName_L2 in ('普通采购/委外入库', '普通采购/委外退货', '普通采购收货检验合格入库') and b.FMaterialID = FMaterialID) loop re := re || c1.fname; end loop; RETURN(regexp_replace(re, substr(re, 1, 1), '', 1, 1)); end;
select lj_getsupply(FMaterialID,to_date('2014-01-01','yyyy-mm-dd'),to_date('2014-12-31','yyyy-mm-dd') )as fsupply from T_IM_PurInWarehsEntry
select lj_getsupply(to_char(a.FMaterialID),to_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd'),to_date('2014-07-31','yyyy-mm-dd')) as fsupply from T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID where a.FMaterialID ='+WAAAAAA+zdECefw' 我这个语句,为什么一直报 literal does not match format string 错误。。
select lj_getsupply(to_char(FMaterialID),b.fbizdate,b.fbizdate) as fsupply from T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID where a.FMaterialID ='+WAAAAAA+zdECefw' 为什么会抛出一个结果集还不是一个字符串呢
我只是想,把本月第一天和我的参数@fdate写到里面去。。怎么都不成功 select lj_getsupply(to_char(FMaterialID),to_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd'),sysdate) as fsupply from T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID where a.FMaterialID ='+WAAAAAA+zdECefw'一直报 literal does not match format string 错误。。
你写的太复杂了。 last_day('2014-02-01')这里有错,last_day的参数应该是一个日期类型,你传入的是一个字符型。 o_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd')不知道你这个表达式想求什么,如果你想去当月第一天的话,如下: trunc(sysdate,'mm') 你就可以这样来执行:select lj_getsupply(to_char(a.FMaterialID), trunc(sysdate, 'mm'), sysdate) as fsupply from T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID where a.FMaterialID = '+WAAAAAA+zdECefw'
你写的太复杂了。 last_day('2014-02-01')这里有错,last_day的参数应该是一个日期类型,你传入的是一个字符型。 o_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd')不知道你这个表达式想求什么,如果你想去当月第一天的话,如下: trunc(sysdate,'mm') 你就可以这样来执行:select lj_getsupply(to_char(a.FMaterialID), trunc(sysdate, 'mm'), sysdate) as fsupply from T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID where a.FMaterialID = '+WAAAAAA+zdECefw' 试了,没有结果。单独执行sql是有结果的大神
select lj_getsupply('+WAAAAAA+zdECefw','2014-06-01','2014-06-08') as fsupply from T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID where a.FMaterialID ='+WAAAAAA+zdECefw'oracle 我直接这样写,调用函数,参数完全没有起到过滤作用啊,神呐
select lj_getsupply('+WAAAAAA+zdECefw',to_date('2014-06-01','yyyy-mm-dd'),to_date('2014-06-08','yyyy-mm-dd')) as fsupply from dual; 还不行我也没办法了,因为我没有你的数据,无法模拟你想要什么结果,只能保证你的语法没错
create or replace FUNCTION lj_getsupply(FMaterialID varchar2,
fdate1 date,
fdate2 date) return varchar2 is
re varchar2(100);
begin
for c1 in (select distinct e.fname_l2 fname from T_IM_PurInWarehsBill a
inner join T_IM_PurInWarehsEntry b
on a.fid = b.FParentID
inner join T_SCM_BizType c
on a.FBizTypeID = c.fid
inner join T_SCM_TransactionType d
on a.FTransactionTypeID = d.FID
inner join T_BD_Supplier e
on a.FSupplierID = e.fid
where a.FBizDate >= fdate1
and a.FBizDate <= fdate2
and c.FName_L2 in ('普通采购', '普通采购退货')
and d.FName_L2 in ('普通采购/委外入库',
'普通采购/委外退货',
'普通采购收货检验合格入库')
and b.FMaterialID = FMaterialID) loop
re := re || c1.fname;
end loop;
RETURN(regexp_replace(re, substr(re, 1, 1), '', 1, 1));
end;select lj_getsupply(FMaterialID,'2014-01-01','2014-12-31') as fsupply from T_IM_PurInWarehsEntry
select lj_getsupply(FMaterialID,to_date('2014-01-01','yyyy-mm-dd'),to_date('2014-12-31','yyyy-mm-dd') as fsupply from T_IM_PurInWarehsEntry
select lj_getsupply(FMaterialID,to_date('2014-01-01','yyyy-mm-dd'),to_date('2014-12-31','yyyy-mm-dd') as fsupply from T_IM_PurInWarehsEntry
提示 missing right parenthesis
fdate1 date,
fdate2 date) return varchar2 is
re varchar2(1500);
begin
for c1 in (select distinct e.fname_l2 fname
from T_IM_PurInWarehsBill a
inner join T_IM_PurInWarehsEntry b
on a.fid = b.FParentID
inner join T_SCM_BizType c
on a.FBizTypeID = c.fid
inner join T_SCM_TransactionType d
on a.FTransactionTypeID = d.FID
inner join T_BD_Supplier e
on a.FSupplierID = e.fid
where a.FBizDate >= fdate1
and a.FBizDate <= fdate2
and c.FName_L2 in ('普通采购', '普通采购退货')
and d.FName_L2 in ('普通采购/委外入库',
'普通采购/委外退货',
'普通采购收货检验合格入库')
and b.FMaterialID = FMaterialID) loop
re := re || c1.fname;
end loop;
RETURN(regexp_replace(re, substr(re, 1, 1), '', 1, 1));
end;
select lj_getsupply(FMaterialID,to_date('2014-01-01','yyyy-mm-dd'),to_date('2014-12-31','yyyy-mm-dd') )as fsupply from T_IM_PurInWarehsEntry
我测过了,单独SQL,我直接写sql语句,查出来结果集是对的,经过了日期过滤条件的
我测过了,单独SQL,我直接写sql语句,查出来结果集是对的,经过了日期过滤条件的
能给我看看你单独测试的SQL吗?
select lj_getsupply(to_char(a.FMaterialID),to_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd'),to_date('2014-07-31','yyyy-mm-dd')) as fsupply
from
T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID
where a.FMaterialID ='+WAAAAAA+zdECefw'
我这个语句,为什么一直报 literal does not match format string 错误。。
select lj_getsupply(to_char(FMaterialID),b.fbizdate,b.fbizdate) as fsupply
from
T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID
where a.FMaterialID ='+WAAAAAA+zdECefw'
为什么会抛出一个结果集还不是一个字符串呢
select lj_getsupply(to_char(FMaterialID),to_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd'),sysdate) as fsupply
from
T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID
where a.FMaterialID ='+WAAAAAA+zdECefw'一直报 literal does not match format string 错误。。
last_day('2014-02-01')这里有错,last_day的参数应该是一个日期类型,你传入的是一个字符型。
o_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd')不知道你这个表达式想求什么,如果你想去当月第一天的话,如下:
trunc(sysdate,'mm')
你就可以这样来执行:select lj_getsupply(to_char(a.FMaterialID), trunc(sysdate, 'mm'), sysdate) as fsupply
from T_IM_PurInWarehsEntry a
inner join T_IM_PurInWarehsBill b
on b.fid = a.FParentID
where a.FMaterialID = '+WAAAAAA+zdECefw'
last_day('2014-02-01')这里有错,last_day的参数应该是一个日期类型,你传入的是一个字符型。
o_date(to_char(trunc(add_months(last_day('2014-02-01'), -1) + 1), 'yyyy-mm-dd'), 'yyyy-mm-dd')不知道你这个表达式想求什么,如果你想去当月第一天的话,如下:
trunc(sysdate,'mm')
你就可以这样来执行:select lj_getsupply(to_char(a.FMaterialID), trunc(sysdate, 'mm'), sysdate) as fsupply
from T_IM_PurInWarehsEntry a
inner join T_IM_PurInWarehsBill b
on b.fid = a.FParentID
where a.FMaterialID = '+WAAAAAA+zdECefw'
试了,没有结果。单独执行sql是有结果的大神
select lj_getsupply('+WAAAAAA+zdECefw','2014-06-01','2014-06-08') as fsupply
from
T_IM_PurInWarehsEntry a inner join T_IM_PurInWarehsBill b on b.fid = a.FParentID
where a.FMaterialID ='+WAAAAAA+zdECefw'oracle 我直接这样写,调用函数,参数完全没有起到过滤作用啊,神呐
select lj_getsupply('+WAAAAAA+zdECefw',to_date('2014-06-01','yyyy-mm-dd'),to_date('2014-06-08','yyyy-mm-dd')) as fsupply
from dual;
还不行我也没办法了,因为我没有你的数据,无法模拟你想要什么结果,只能保证你的语法没错