包头在这里: CREATE OR REPLACE package ZXF.Necktie is --Public type declarations, cursor variable type sale_outList is ref cursor; --变量定义: ct1 zxf.csch.hzrq%type; ct2 zxf.csch.hzrq%type; st1 zxf.sfjl.sfrq%type; st2 zxf.sfjl.sfrq%type; v_sql varchar2(1000); PROCEDURE sp_Necktie_GetSaleList(s_date1 in zxf.hxda.hxmc%type,s_date2 in zxf.hxda.hxmc%type, NecktieList out sale_outList);
end Necktie; / 所以现在的问题聚集在日期列的引用,出了问题 jiezhi(浪子) 高手再帮我一把
FOR 'select a.hxbm as hxbm, (select nvl(sum(inum),0) from zxf.sfjl where sfrq>= '||st1||' and sfrq <='||st2||' and sflb=1 and hxbm=a.hxbm) as xsl from zxf.hxda a order by hxbm,xsl desc';
由 st1 改成 ZXF.Necktie.st1 试试?
to: beckhambobo(beckham) 我已经按照你的'||st1||'用了,但是出现错误“缺少右括号” 我用字符型的变量如此引用的话,绝对没有问题的,所以现在的问题就剩下日期类型的引用了 谢谢,请再帮我吧
select sfrq into st1 from (select * from zxf.sfjl where sfrq <=to_date(s_date1, 'yyyy/mm/dd hh24:mi:ss') order by sfrq desc) where rownum = 1;select sfrq into st2 from (select * from zxf.sfjl where sfrq <=to_date(s_date2, 'yyyy/mm/dd hh24:mi:ss') order by sfrq desc) where rownum = 1;
select a.hxbm,(select sum(nvl(inum,0)) from zxf.sfjl where sfrq>=... and sfrq <=... and sflb=1 and hxbm=a.hxbm) xsl from zxf.hxda a order by hxbm,xsl desc; 省略号给它放几个值去。看看这条语句能不能执行
我把那条语句换成这样: (select nvl(sum(inum),0) from zxf.sfjl where sfrq<= '||to_date('||2010-09-09||','yyyy/mm/dd hh24:mi:ss')||' and sfrq>= '||to_date('||2000-09-09||','yyyy/mm/dd hh24:mi:ss')||' and sflb=1 and hxbm=a.hxbm) 提示错误变成: ORA-01841: (全)年度值必须介于 -4713 和 +9999 之间,且不为 0
'select a.hxbm,(select sum(nvl(inum,0)) from zxf.sfjl where sfrq>= to_date(''2010-09-09'',''yyyy-mm-dd'') and sfrq <=to_date(''2000-09-09'',''yyyy-mm-dd'') and sflb=1 and hxbm=a.hxbm) xsl from zxf.hxda a order by hxbm,xsl desc';
老大快帮帮忙啊,现在我把它写成最简单的,什么都不处理,就是引用日期变量: for 'select hxbm,inum from zxf.sfjl where sfrq<='||ct1||' and sflb=1'; 说:ORA-00933: SQL 命令未正确结束!!! 救命啊,到底怎么引用日期变量啊 难道没有一个人搞过吗?????
测试一下: 把yyyy/mm/dd 换成 YYYY-MM-DD(select nvl(sum(inum),0) from zxf.sfjl where sfrq<= '||to_date('||2010-09-09||','YYYY-MM-DD')||' and sfrq>= '||to_date('||2000-09-09||','YYYY-MM-DD')||' and sflb=1 and hxbm=a.hxbm)
thanks to :beckhambobo(beckham) 'select a.hxbm,(select sum(nvl(inum,0)) from zxf.sfjl where sfrq>= to_date(''2010-09-09'',''yyyy-mm-dd'') and sfrq <=to_date(''2000-09-09'',''yyyy-mm-dd'') and sflb=1 and hxbm=a.hxbm) xsl from zxf.hxda a order by hxbm,xsl desc'; 你的方法是对的!!! 现在问题是变量还是不行,放进去就说: ORA-01841: (全)年度值必须介于 -4713 和 +9999 之间,且不为 0
for 'select hxbm,inum from zxf.sfjl where to_char(sfrq,''YYYY-MM-DD'')<=to_char('''||ct1||',''YYYY-MM-DD''), and sflb=1';
写错了 for 'select hxbm,inum from zxf.sfjl where to_char(sfrq,''YYYY-MM-DD'')<=to_char('''||ct1||''',''YYYY-MM-DD''), and sflb=1'; ------------''
非常非常感谢:onejune4450(中文字符) beckhambobo(beckham) 我的问题已经解决了: (select nvl(sum(inum),0) from zxf.sfjl where sfrq<= to_date('''||to_char(st2,'yyyy-mm-dd')||''',''yyyy-mm-dd'') and sflb=1 and hxbm=a.hxbm)
CREATE OR REPLACE package ZXF.Necktie is --Public type declarations, cursor variable
type sale_outList is ref cursor;
--变量定义:
ct1 zxf.csch.hzrq%type;
ct2 zxf.csch.hzrq%type; st1 zxf.sfjl.sfrq%type;
st2 zxf.sfjl.sfrq%type;
v_sql varchar2(1000); PROCEDURE sp_Necktie_GetSaleList(s_date1 in zxf.hxda.hxmc%type,s_date2 in zxf.hxda.hxmc%type, NecktieList out sale_outList);
end Necktie;
/
所以现在的问题聚集在日期列的引用,出了问题
jiezhi(浪子) 高手再帮我一把
(select nvl(sum(inum),0) from zxf.sfjl where sfrq>= '||st1||' and sfrq <='||st2||' and sflb=1 and hxbm=a.hxbm) as xsl
from zxf.hxda a
order by hxbm,xsl desc';
我已经按照你的'||st1||'用了,但是出现错误“缺少右括号”
我用字符型的变量如此引用的话,绝对没有问题的,所以现在的问题就剩下日期类型的引用了
谢谢,请再帮我吧
省略号给它放几个值去。看看这条语句能不能执行
(select nvl(sum(inum),0) from zxf.sfjl where sfrq<= '||to_date('||2010-09-09||','yyyy/mm/dd hh24:mi:ss')||' and sfrq>= '||to_date('||2000-09-09||','yyyy/mm/dd hh24:mi:ss')||' and sflb=1 and hxbm=a.hxbm)
提示错误变成:
ORA-01841: (全)年度值必须介于 -4713 和 +9999 之间,且不为 0
from zxf.hxda a
order by hxbm,xsl desc';
for 'select hxbm,inum from zxf.sfjl where sfrq<='||ct1||' and sflb=1';
说:ORA-00933: SQL 命令未正确结束!!!
救命啊,到底怎么引用日期变量啊
难道没有一个人搞过吗?????
把yyyy/mm/dd 换成 YYYY-MM-DD(select nvl(sum(inum),0) from zxf.sfjl
where sfrq<= '||to_date('||2010-09-09||','YYYY-MM-DD')||'
and sfrq>= '||to_date('||2000-09-09||','YYYY-MM-DD')||'
and sflb=1 and hxbm=a.hxbm)
'select a.hxbm,(select sum(nvl(inum,0)) from zxf.sfjl where sfrq>= to_date(''2010-09-09'',''yyyy-mm-dd'') and sfrq <=to_date(''2000-09-09'',''yyyy-mm-dd'') and sflb=1 and hxbm=a.hxbm) xsl
from zxf.hxda a
order by hxbm,xsl desc';
你的方法是对的!!!
现在问题是变量还是不行,放进去就说:
ORA-01841: (全)年度值必须介于 -4713 和 +9999 之间,且不为 0
for 'select hxbm,inum from zxf.sfjl where to_char(sfrq,''YYYY-MM-DD'')<=to_char('''||ct1||''',''YYYY-MM-DD''), and sflb=1';
------------''
beckhambobo(beckham)
我的问题已经解决了:
(select nvl(sum(inum),0) from zxf.sfjl where sfrq<= to_date('''||to_char(st2,'yyyy-mm-dd')||''',''yyyy-mm-dd'') and sflb=1 and hxbm=a.hxbm)