小弟是一菜菜鸟,最近修改一个程序,要加入一个功能,在依样画葫芦的过程中碰到这个问题:
原代码如下:
sql2 = "select substr(sljsdw,1,"&dwhow&") as lrdw,count(*) as howzs,sum(decode(ErrorID,0,0,1)) as howbxx,sum(decode(xzcs,null,1,0)) as howxzcs,sum(decode(XZBW,null,1,0)) as howxzbw from"
sql2 = sql2 & " (select t.*,decode(xzcs,null,1,0)+decode(XZBW,null,1,0) as ErrorID from b_asj_aj t where deleteflag='0' and ajlx='01' and fasjzz>=to_date('"&qssj&"','yyyy-mm-dd') and fasjzz<=to_date('"&jssj&"','yyyy-mm-dd') and sljsdw like '"&lrdw&"%')"
sql2 = sql2 & " group by substr(sljsdw,1,"&dwhow&")"
小弟加入sql3,内容如下:
sql3 = "select substr(sljsdw,1,"&dwhow&") as lrdw,count(*) as howzs,sum(decode(sign(sljjsj+1-sl_lrsj),-1,1,0)) as howbjs from b_asj_aj t where deleteflag='0' and ajlx='01' and fasjzz>=to_date('"&qssj&"','yyyy-mm-dd') and fasjzz<=to_date('"&jssj&"','yyyy-mm-dd') and sljsdw like '"&lrdw&"%')"
sql3 = sql3 & " group by substr(sljsdw,1,"&dwhow&")"
如果将sum(decode(sign(sljjsj+1-sl_lrsj),-1,1,0)) as howbjs直接写入sql2中可以运行,可是独立到sql3中却老是提示出错。小弟最近已是心力交瘁,望哪位高手能帮我指点指点啊,谢谢!
原代码如下:
sql2 = "select substr(sljsdw,1,"&dwhow&") as lrdw,count(*) as howzs,sum(decode(ErrorID,0,0,1)) as howbxx,sum(decode(xzcs,null,1,0)) as howxzcs,sum(decode(XZBW,null,1,0)) as howxzbw from"
sql2 = sql2 & " (select t.*,decode(xzcs,null,1,0)+decode(XZBW,null,1,0) as ErrorID from b_asj_aj t where deleteflag='0' and ajlx='01' and fasjzz>=to_date('"&qssj&"','yyyy-mm-dd') and fasjzz<=to_date('"&jssj&"','yyyy-mm-dd') and sljsdw like '"&lrdw&"%')"
sql2 = sql2 & " group by substr(sljsdw,1,"&dwhow&")"
小弟加入sql3,内容如下:
sql3 = "select substr(sljsdw,1,"&dwhow&") as lrdw,count(*) as howzs,sum(decode(sign(sljjsj+1-sl_lrsj),-1,1,0)) as howbjs from b_asj_aj t where deleteflag='0' and ajlx='01' and fasjzz>=to_date('"&qssj&"','yyyy-mm-dd') and fasjzz<=to_date('"&jssj&"','yyyy-mm-dd') and sljsdw like '"&lrdw&"%')"
sql3 = sql3 & " group by substr(sljsdw,1,"&dwhow&")"
如果将sum(decode(sign(sljjsj+1-sl_lrsj),-1,1,0)) as howbjs直接写入sql2中可以运行,可是独立到sql3中却老是提示出错。小弟最近已是心力交瘁,望哪位高手能帮我指点指点啊,谢谢!
解决方案 »
- oracle数据库起不来了。。。nomount也起不来。。。。具体情况向下看
- oracle11g Exp Imp 后,发现表少了很多
- oracle9i中sqlloader的问题:无效数据
- SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
- oracle常用的应用语句有那些大家给提供下如:建库,建表,备份,恢复
- 怎样停掉Oracle Servlet Engine服务?
- 请 njhart2003 取分.
- oracle 9.2.0 在Redhat 7.3 下安装出错
- 各位我还有一个问题:如何在VB中用代码建立一个oracle数据源:在线等待!
- 请教SQL,是否可以根据某列 数据分组显示
- red flag6下安装oracle10g,每次安装到62%时总卡在linking ntcontab这里
- 大量的图片要插入oracle,为了实时,怎样提高插入速度
调试的时候应该把sql3最终的语句拿出来到oracle上执行一下,就很容易看出问题了.我看了以下,第一句sql3最后一个括号多了,改一下应该就可以了
sql3 = "select substr(sljsdw,1,"&dwhow&") as lrdw,count(*) as howzs,sum(decode(sign(sljjsj+1-sl_lrsj),-1,1,0)) as howbjs from b_asj_aj t where deleteflag='0' and ajlx='01' and fasjzz>=to_date('"&qssj&"','yyyy-mm-dd') and fasjzz <=to_date('"&jssj&"','yyyy-mm-dd') and sljsdw like '"&lrdw&"%'"
sql2 = "select substr(sljsdw,1,"&dwhow&") as lrdw,count(*) as howzs,sum(decode(ErrorID,0,0,1)) as howbxx,sum(decode(xzcs,null,1,0)) as howxzcs,sum(decode(XZBW,null,1,0)) as howxzbw,sum(decode(sign(sljjsj+1-sl_lrsj),-1,1,0)) as howbjs from"
sql2 = sql2 & " (select t.*,decode(xzcs,null,1,0)+decode(XZBW,null,1,0) as ErrorID from b_asj_aj t where deleteflag='0' and ajlx='01' and fasjzz>=to_date('"&qssj&"','yyyy-mm-dd') and fasjzz <=to_date('"&jssj&"','yyyy-mm-dd') and sljsdw like '"&lrdw&"%')"
sql2 = sql2 & " group by substr(sljsdw,1,"&dwhow&")"