AS/400 SQL: How can I use a sum() to search for records in a file?
I am trying to use the SQL statement to sum up a total and use the total to compare to another record in the same file. My SQL statement is:
select w1styl,sum(w1actc) from fileA
where w1actc > 0
and sum(w1actc) <=
(select w1remc from filea
where wmactc = 0)
group by w1styl
This sums up the total amount [sum(w1actc)] by STYL and hunts for another record where the sum is less than the remaining amount [w1remc] of another record and nothing in the location [w1actc=0].
The goal is to find multiple locations in a location file that can fit in another location.
Thanks.I was able to make the select statement work using the having clause. However, it ran into the error of finding more than one row that met the condition 'Result of select more than one row'. Is there a way to disregard how many rows were selected with a true/false condition? In other words, I dont care how many rows were selected, I just want to know if the condition is true.
I dont care how many rows were selected, I just want to know if the condition is true.
What condition? That sub-SELECT only has one condition -- where wmactc = 0. But I'm pretty sure that's not the condition that you mean.
There might be dozens, hundreds or thousands of rows "where wmactc = 0". Which one do you want to test against the sum() value? That's what you need to decide. SQL needs to know which one should be tested to determine if sum(w1actc) is less than that row.
Some of the rows might be greater than; some might be less than. If you don't tell SQL which row to compare against, it has no way to tell when the condition is true.
You have a value -- sum(w1actc). And then you have a set of rows -- select w1remc from filea where wmactc = 0. It looks as if you're trying to compare a single value against a set of values.
Let's say that sum(w1actc) comes out as (5), and your sub-select finds three rows with the values (3, 5, 7). Does that mean that the condition is true or false? It seems it would be both.
If you want to know if your sum is less than all of the values, use MIN(w1remc) in your sub-select. Or if you want to know if it's less than any of the values, use MAX(w1remc).
Tom
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 3  Replies