Error when adding SUM to SELECT stmt

[czab]

The following code works fine when I run it with ONLY the blue code.

SELECT
PRPMED.NCONO AS COMPANY,
PRPMED.NSSNO AS SSN,
PRPMED.NEENO AS EMP_NO,
PRPMED.NAMDE AS DEDN_AMT,
PRPMED.NDENO AS DEDN_NO,
PRPMED.NDTJR AS JNRL_DATE,
HRPMBN.MNAMDE AS BENE_MATCH_AMT,
HRPMBN.MNBNTY AS BENE_TYPE,
HRPMBN.MNBNNO AS BENE_NUM ,
Sum(CASE WHEN HRPMBN.MNBNTY = 2 THEN HRPMBN.MNAMDE END) AS Contrib

FROM PRPMED LEFT JOIN HRPMBN ON
(PRPMED.NCONO = HRPMBN.MNCONO) AND
(PRPMED.NSSNO = HRPMBN.MNSSNO) AND
(PRPMED.NEENO = HRPMBN.MNEENO)

WHERE
PRPMED.NDTJR = @variable('Journal Date (yyyymmdd)') and
HRPMBN.MNDTJR = @variable('Journal Date (yyyymmdd)') AND
(PRPMED.NDENO in (20,21,22,701,702,703) OR
HRPMBN.MNBNNO in (401,410))
GROUP BY PRPMED.NCONO, PRPMED.NSSNO, PRPMED.NEENO


When I add just the red section I get an error message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0122 - Column NCONO or expression in SELECT list not valid.-122"

When I add the red AND green sections I get an error message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0122 - Column NAMDE or expression in SELECT list not valid.-122"

Can anybody please tell me what I'm doing wrong?

 

[r937]

you didn't GROUP BY all the non-aggregate columns in the SELECT

every column that isn't in a SUM() or COUNT() or AVG() expression must be included in the GROUP BY

r937.com | rudy.ca

 

[czab]

Thanks r937. That was my problem. Now that I have that figured out, I created a new problem. My numbers aren't adding up correctly.

My new code is this:

SELECT
PRPMED.NCONO AS COMP,
PRPMED.NSSNO AS SSN,
PRPMED.NEENO AS EMP_NO,
Sum(CASE WHEN HRPMBN.MNBNTY = 2 THEN HRPMBN.MNAMDE END) AS CONTRIB,
Sum(CASE WHEN HRPMBN.MNBNTY = 3 THEN HRPMBN.MNAMDE END) AS MATCH,
Sum(CASE WHEN PRPMED.NDENO in (710,20) then PRPMED.NAMDE end) AS LOAN1,
Sum(CASE WHEN PRPMED.NDENO in (711,22) then PRPMED.NAMDE end) AS LOAN2,
Sum(CASE WHEN PRPMED.NDENO in (712,21) then PRPMED.NAMDE end) AS LOAN3

FROM PRPMED LEFT JOIN HRPMBN ON
(PRPMED.NCONO = HRPMBN.MNCONO) AND
(PRPMED.NSSNO = HRPMBN.MNSSNO) AND
(PRPMED.NEENO = HRPMBN.MNEENO)

WHERE
PRPMED.NDTJR = @variable('Journal Date (yyyymmdd)') and
HRPMBN.MNDTJR = @variable('Journal Date (yyyymmdd)') AND
(PRPMED.NDENO in (20,21,22,710,712,711) OR
HRPMBN.MNBNNO in (401,410))
GROUP BY PRPMED.NCONO, PRPMED.NSSNO, PRPMED.NEENO

Table: PRPMED
NCONO NSSNO NEENO NDENO NAMDE
3 123 31150 710 59.85
3 123 31150 711 19.80

Table: HRPMBN
MNCONO MNSSNO MNEENO MNBNNO MNBNTY MNAMDE
3 123 31150 410 2 52.98
3 123 31150 410 3 26.49

Expected result:
COMP SSN EMP_NO CONTRIB MATCH LOAN1 LOAN2 LOAN3
3 123 31150 52.98 26.49 59.85 19.80 0.00

Query result returned:
COMP SSN EMP_NO CONTRIB MATCH LOAN1 LOAN2 LOAN3
3 123 31150 425.90 158.4 239.40 79.20 0.00

I don't know what I'm doing wrong.
There are instances where an employee will have a record in both tables but sometimes an employee will have a record in ONLY one of the tables and it may be EITHER one of them.

Please help! I need to get this done today.

 

[r937]

you appear to be getting cross join effects (i haven't actually done the math on your examples, but that's what it looks like)

your join produces 4 intermediate rows ( each of 2 rows in one table matching both 2 rows in the other )

r937.com | rudy.ca

 

[czab]

I decided to make two separate queries and let business objects bring them together. It was a piece of cake to do it that way, but I'm still frustrated with myself that I couldn't get it to work in one query.