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

Question

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?

Gordon Linoff · Accepted Answer · 2017-10-26 01:46:22Z

2

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
        );

answered Oct 26, 2017 at 1:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dave Over a year ago

Thanks for the quick reply, Gordon. I had actually forgot to put my sums in my example, but they were in my original example. I edited my original comment to reflect that. If I move the sums to before the case statement I get this error: Number of arguments for function SUM not valid.

Dave Over a year ago

It just doesn't like the fact that the date isn't part of the grouping, but is instead being used for the selecting in the top part of the SQL clause instead of after the Where statement. I'm thinking there might be a way with the WITH statement or something, but I just can't figure it out. Thanks for your help though!

Gordon Linoff Over a year ago

The date (at least InvDate is only being used as an argument to sum(). There should be no problem with it.

Dave Over a year ago

Hi Gordon, yeah, I would have thought it wouldn't matter but I'm still getting that same error: Column InvDate or expression in SELECT list not valid. I'm reduced to putting each field I want to update in it's own SQL statement, which just kills me! Haha

jmarkmurphy Over a year ago

@GordonLinoff can you vote for [db2i] to be a synonym of [db2400]

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related