Bluebelief
2009-06-19 11:28
浏览 288
已采纳

oracle decode函数的问题, 急待解决

我想更新一个表的字段,可是写完报ora-00936缺少表达式,代码贴出来,高手帮着看看,小生感激不尽

update GATHER_STATISTICS t
set BATCH_SUCCESS_COUNT = select decode(cnt, 0,0) as cnt
from ((SELECT count(g.WARNING_TYPE) as cnt
from jk_gather_info_history g
where g.WARNING_TYPE = 0
and g.batch_id = t.batch_id
and t.userid = 2
and g.gather_date >=
to_date('2009-4-7 16:24:24',
'yyyy-mm-dd HH24:MI:SS')
and g.gather_date <=
to_date('2009-4-7 16:24:27',
'yyyy-mm-dd HH24:MI:SS')
group by t.batch_id))
[b]问题补充:[/b]
感谢lovewhzlq的回答,但是还是不行,还是报同样的错误

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • wanghaolovezlq 2009-06-19 11:39
    已采纳

    update GATHER_STATISTICS t
    set BATCH_SUCCESS_COUNT = select decode([color=red]c.cnt[/color], 0,0) as cnt
    from ((SELECT count(g.WARNING_TYPE) as cnt
    from jk_gather_info_history g
    where g.WARNING_TYPE = 0
    and g.batch_id = t.batch_id
    and t.userid = 2
    and g.gather_date >=
    to_date('2009-4-7 16:24:24',
    'yyyy-mm-dd HH24:MI:SS')
    and g.gather_date <=
    to_date('2009-4-7 16:24:27',
    'yyyy-mm-dd HH24:MI:SS')
    group by t.batch_id))[color=red] c[/color]

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • wanghaolovezlq 2009-06-19 11:38

    这样试试

    [code="java"]
    update GATHER_STATISTICS t
    set BATCH_SUCCESS_COUNT = select decode(c.cnt, 0,0) as cnt
    from ((SELECT count(g.WARNING_TYPE) as cnt
    from jk_gather_info_history g
    where g.WARNING_TYPE = 0
    and g.batch_id = t.batch_id
    and t.userid = 2
    and g.gather_date >=
    to_date('2009-4-7 16:24:24',
    'yyyy-mm-dd HH24:MI:SS')
    and g.gather_date <=
    to_date('2009-4-7 16:24:27',
    'yyyy-mm-dd HH24:MI:SS')
    group by t.batch_id))

    [/code]

    评论
    解决 无用
    打赏 举报
  • iteye_453 2009-06-19 14:54

    看了下Lz的问题 看下下面的Sql能运行不

    update GATHER_STATISTICS t
    set BATCH_SUCCESS_COUNT =

    select decode(cnt, 0, 0)
    from ((SELECT count(g.WARNING_TYPE) cnt
    from jk_gather_info_history g, GATHER_STATISTICS m
    where g.WARNING_TYPE = 0 and g.batch_id = m.batch_id and
    m.userid = 2 and
    g.gather_date >=
    to_date('2009-4-7 16:24:24', 'yyyy-mm-dd HH24:MI:SS') and
    g.gather_date <=
    to_date('2009-4-7 16:24:27', 'yyyy-mm-dd HH24:MI:SS')
    group by m.batch_id)) a

    :wink: :wink:

    评论
    解决 无用
    打赏 举报
  • iteye_453 2009-06-19 14:56

    不好意思上面可能还有点小问题 改成

    update GATHER_STATISTICS t
    set BATCH_SUCCESS_COUNT =
    select decode([color=red]a[/color].cnt, 0, 0)
    from ((SELECT count(g.WARNING_TYPE) cnt
    from jk_gather_info_history g, GATHER_STATISTICS m
    where g.WARNING_TYPE = 0 and g.batch_id = m.batch_id and
    m.userid = 2 and
    g.gather_date >=
    to_date('2009-4-7 16:24:24', 'yyyy-mm-dd HH24:MI:SS') and
    g.gather_date <=
    to_date('2009-4-7 16:24:27', 'yyyy-mm-dd HH24:MI:SS')
    group by m.batch_id)) [color=red]a[/color]

    LZ再看看

    评论
    解决 无用
    打赏 举报
  • wanghaolovezlq 2009-06-29 08:16

    朋友,问题要自动关闭啦,结分哦

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题