Derived field in IDO as a SUM value?

Eastwood
Eastwood Member Posts: 9
in Mongoose

We are trying to create a SUM of the qty shipped column for blanket orders and if not blanket we use the qty shipped in the coitem table.   below is what we have we have never done this can anyone please help, we get a rowpointer error.

case when type ='B' then
(select sum(qty_shipped) from coitem_mst where coitem_mst.co_num = co_num and coitem_mst.co_line = coblnco_line group by coitem_mst.co_num)
Else
Coitemqty_shipped
end


Answers

  • Carsten Steinhoefel
    Carsten Steinhoefel Member, Staff Posts: 26

    Two things:

    • I recommend you use the views instead of the tables directly (i.e. coitem not coitem_mst). If you need to get data from all sites use coitem_all.
    • For comparing to the current record in the collection you can use property names instead of column names so e.g. CoNum and CoLine.

    Also, you only want one value back, so no need for the grouping. I would try this (Disclaimer: Not tested):

    case when Type='B' then

    (select SUM(qty_shipped) from coitem where co_num = CoNum and co_line = CoLine)

    else qty_shipped end

Categories