×

Loading...
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

@

SQL 高手请帮忙!!

memeling(Double M)
Run a query on Oracle, 但有ORA-00933 error (SQL command .doesnt end properly)
本人水平有限, 不知如何重写.
SQL 高手请帮忙!!

SELECT
a.member_id,
flag,
sum(a.value_amt) as total_spend
FROM
master.f_purchase_category b,
master.d_date c,
WHERE b.d_trans_date_key=c.d_date_key
and c.cal_dt >= to_date ('2006-01-01','yyyy-mm-dd')
and c.cal_dt < to_date ('2006-03-20','yyyy-mm-dd')
group by a.member_id,
CASE when c.cal_dt >= to_date('2006-01-01','yyyy-mm-dd')
and c.cal_dt < to_date('2006-02-01','yyyy-mm-dd')
THEN '1'
when c.cal_dt >= to_date('2006-02-01','yyyy-mm-dd')
and c.cal_dt < to_date('2006-03-01','yyyy-mm-dd')
THEN '2'
ELSE '3'
end as flag
(#2862940@0)
Last Updated: 2006-3-24
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业知识杂谈 / SQL 高手请帮忙!! -memeling(Double M); 2006-3-24 {822} (#2862940@0)
    Run a query on Oracle, 但有ORA-00933 error (SQL command .doesnt end properly)
    本人水平有限, 不知如何重写.
    SQL 高手请帮忙!!

    SELECT
    a.member_id,
    flag,
    sum(a.value_amt) as total_spend
    FROM
    master.f_purchase_category b,
    master.d_date c,
    WHERE b.d_trans_date_key=c.d_date_key
    and c.cal_dt >= to_date ('2006-01-01','yyyy-mm-dd')
    and c.cal_dt < to_date ('2006-03-20','yyyy-mm-dd')
    group by a.member_id,
    CASE when c.cal_dt >= to_date('2006-01-01','yyyy-mm-dd')
    and c.cal_dt < to_date('2006-02-01','yyyy-mm-dd')
    THEN '1'
    when c.cal_dt >= to_date('2006-02-01','yyyy-mm-dd')
    and c.cal_dt < to_date('2006-03-01','yyyy-mm-dd')
    THEN '2'
    ELSE '3'
    end as flag
    • 怎么没见a? -markriver(markriver); 2006-3-24 (#2862960@0)
      • Should be "b". Sorry. -memeling(Double M); 2006-3-24 (#2862987@0)
        • no where , having -markriver(markriver); 2006-3-24 (#2863264@0)
    • master.d_date c, 不应该有这个 "," -hard20(hard20); 2006-3-24 {567} (#2863089@0)
      SELECT
      b.member_id,
      CASE when c.cal_dt >= to_date('2006-01-01','yyyy-mm-dd')
      and c.cal_dt < to_date('2006-02-01','yyyy-mm-dd')
      THEN '1'
      when c.cal_dt >= to_date('2006-02-01','yyyy-mm-dd')
      and c.cal_dt < to_date('2006-03-01','yyyy-mm-dd')
      THEN '2'
      ELSE '3'
      end as flag ,
      sum(a.value_amt) as total_spend
      FROM
      master.f_purchase_category b,
      master.d_date c
      WHERE b.d_trans_date_key=c.d_date_key
      and c.cal_dt >= to_date ('2006-01-01','yyyy-mm-dd')
      and c.cal_dt < to_date ('2006-03-20','yyyy-mm-dd')
      group by a.member_id, flag
      • 好像还是不行. -memeling(Double M); 2006-3-24 (#2863395@0)
      • 没装Oracle,没法帮你试,看看这个好不好用吧... -hard20(hard20); 2006-3-24 {332} (#2863526@0)
        SELECT
        b.member_id,
        to_char(c.cal_dt,'mm'),
        sum(a.value_amt) as total_spend
        FROM
        master.f_purchase_category b,master.d_date c
        WHERE b.d_trans_date_key=c.d_date_key
        and c.cal_dt >= to_date ('2006-01-01','yyyy-mm-dd')
        and c.cal_dt < to_date ('2006-03-20','yyyy-mm-dd')
        group by b.member_id, to_char(c.cal_dt,'mm')
    • remove "as flag" from the end should work -tootoo(倚老卖老); 2006-3-27 (#2868162@0)
    • Solved. THanks for your help. -memeling(Double M); 2006-3-27 (#2868515@0)
      • 怎么解决的也不说说? -markriver(markriver); 2006-3-28 (#2870497@0)

More Topics

  • For those of you, who don’t believe that we have technology to control the weather or cause earth quacks, please watch this video.
  • 美国考古博士生分析激光雷达数据发现失踪的千年玛雅古城
  • Yann LeCun最新公开表示：今年诺贝尔奖颁给AI，是诺奖委员会感到压力的结果，需要承认深度学习的影响。化学奖塞不下更多人，所以只能选了物理, 颁给Hinton和Hopefield勉强合理. 获奖成果玻尔兹曼机和Hopefield网络现在完全无用(completely useless)。
  • Nobel Prize-Winner on AI 'Existential Threat' 辛顿认为我们也许很快就失去对AI的控制。这东西在20年内可能超过人类。
  • CO2 is known as the gas of life according to this Harvard professor - the more CO2 we have the more life there can be on the planet.
    • 枫下论坛主坛 /  工作学习 /  专业知识杂谈