Error with a merge statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Error with a merge statement

  1. #1
    Join Date
    Dec 1999
    Posts
    217

    Error with a merge statement

    Please help with this error:

    merge into cat_ord_sm a
    *
    ERROR at line 1:
    ORA-00913: too many values

    Here is the SQL:
    merge into cat_ord_sm a
    using
    (select c_key, cat_key, p_date, ord_type, source_key, state_key,
    sum(demand_qty) as d_qty, sum(demand_cost) as d_cost, sum(demand_retail) as d_retail,
    sum(can_qty) as c_qty, sum(can_cost) as c_cost, sum(can_retail) as c_retail,
    sum(ship_qty) as s_qty, sum(ship_cost) as s_cost, sum(ship_retail) as s_retail,
    sum(ret_qty) as r_qty, sum(ret_cost) as r_cost, sum(ret_retail) as r_retail,
    sum(ord_count) as o_count, sum(ord_xchg_count) as ox_count from cat_ord_sm_add
    group by c_key, cat_key, p_date, ord_type, source_key, state_key) b
    on (a.c_key = b.c_key
    and a.cat_key = b.cat_key
    and a.p_date = b.p_date
    and a.ord_type = b.ord_type
    and a.source_key = b.source_key
    and a.state_key = b.state_key)
    when matched then update set
    a.demand_qty = a.demand_qty + b.d_qty,
    a.demand_cost = a.demand_cost + b.d_cost,
    a.demand_retail = a.demand_retail + b.d_retail,
    a.can_qty = a.can_qty + b.c_qty,
    a.can_cost = a.can_cost + b.c_cost,
    a.can_retail = a.can_retail + b.c_retail,
    a.ship_qty = a.ship_qty + b.s_qty,
    a.ship_cost = a.ship_cost + b.s_cost,
    a.ship_retail = a.ship_retail + b.s_retail,
    a.ret_qty = a.ret_qty + b.r_qty,
    a.ret_cost = a.ret_cost + b.r_cost,
    a.ret_retail = a.ret_retail + b.r_retail,
    a.ord_count= a.ord_count + b.o_count,
    a.ord_xchg_count = a.ord_xchg_count + b.ox_count
    when not matched then insert
    (a.c_key, a.cat_key, a.p_date, a.ord_type. a.source_key, a.state_key,
    a.demand_qty, a.demand_cost, a.demand_retail, a.can_qty, a.can_cost, a.can_retail,
    a.ship_qty, a.ship_cost, a.ship_retail, a.ret_qty, a.ret_cost, a.ret_retail,
    a.ord_count, a.ord_xchg_count)
    values
    (b.c_key, b.cat_key, b.p_date, b.ord_type, b.source_key, b.state_key,
    b.d_qty, b.d_cost, b.d_retail, b.c_qty, b.c_cost, b.c_retail,
    b.s_qty, b.s_cost, b.s_retail, b.r_qty, b.r_cost, b.r_retail,
    b.o_count, b.ox_count)
    ;

    Your input is highly appreciated.
    Chintzs

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you have a . instead of a comma in the not matched section between a.ord_type and a.source_key

  3. #3
    Join Date
    Dec 1999
    Posts
    217
    Thanks for the catch.

    Chintz

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    Originally posted by davey23uk
    you have a . instead of a comma in the not matched section between a.ord_type and a.source_key
    Damn..how the heck did you spot that?!

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    just kinda jumped out at me, wasnt looking that closely :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width