CODEWITHSUNDEEP
sqlibm-midrangedb2-400
Dave
Dave

Reputation: 21

Trying to use CASE statements to update a file from a grouped file SQL AS/400

I'm completely stuck on how to get around this error: Column InvDate or expression in SELECT list not valid.

I'm trying to update this year and last year's summed amount (along with 8 other amounts) using a single SQL update statement:

update CustStats as c set (c.YTDRevenue, c.LastYearRev) =
(select
 case when h.InvDate >= 20170101 then sum(d.InvAmount) else 0 end, 
 case when h.InvDate >= 20160101 and h.InvDate <= 20161231
        then sum(d.InvAmount) else 0 end
from InvHeader as h join InvDetail as d on d.InvNumber=h.InvNumber
where h.Status = 'FINAL' and h.InvDate >= 20160101
 and c.Customer = h.Customer
group by c.Customer)

Is it possible to do this with one statement or do I need one for each field I want to update?

Upvotes: 2

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I would expect your query to work in DB2. You do have an issue with the GROUP BY and lack of aggregation. Perhaps this will suffice:

update CustStats c
    set (c.YTDRevenue, c.LastYearRev) =
        (select sum(case when h.InvDate >= 20170101 then d.InvAmount else 0 end), 
                sum(case when h.InvDate >= 20160101 and h.InvDate <= 20161231
                         then d.InvAmount else 0 end)
         from InvHeader h join
              InvDetail d
              on d.InvNumber = h.InvNumber
         where h.Status = 'FINAL' and h.InvDate >= 20160101 and
               c.Customer = h.Customer
        );

Upvotes: 2

Related Questions