January 10, 2006 at 4:03 pm
Not sure why I am getting this error below. It has someting to do with my CurrentBalance calculation portion in my INNER JOIN area:
Column 'cb.CurrentBalance' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance]
FROM RMASTER rm
INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(SELECT RMSFILENUM, (SELECT (rb.RMSCHGAMT - rb.RMSRCVPCPL)
+(rb.RMSASSCCST - rb.RMSRCVDCST)
+(rb.RMSACRDINT - rb.RMSRCVDINT)
+(rb.UDCCOSTS1 - rb.UDCRECCS1)
+(rb.UDCCOSTS2 - rb.UDCRECCS2)
+(rb.RMSCOST1 - rb.RMSCOST1R)
+(rb.RMSCOST2 - rb.RMSCOST2R)
+(rb.RMSCOST3 - rb.RMSCOST3R)
+(rb.RMSCOST4 - rb.RMSCOST4R)
+(rb.RMSCOST5 - rb.RMSCOST5R)
+(rb.RMSCOST6 - rb.RMSCOST6R)
+(rb.RMSCOST7 - rb.RMSCOST7R)
+(rb.RMSCOST8 - rb.RMSCOST8R)
+(rb.RMSCOST9 - rb.RMSCOST9R)
+(rb.RMSCOST10 - rb.RMSCOST10R)
- rb.RMSXCSRCVS
FROM RPRDBAL rb) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
WHERE rm.rmsacctnum = '4313030999894992'
GROUP BY rm.rmsacctnum, rf10.rmstranamt10
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00
January 10, 2006 at 6:42 pm
ok, I've figured it out:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt) AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
INNER JOIN
(
SELECT RMSFILENUM,
SUM(rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(
SELECT RMSFILENUM,
RMSTRANCDE,
SUM(rmstranamt) AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM, RMSTRANCDE
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS
) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
GROUP BY rm.rmsacctnum, cb.CurrentBalance
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00
January 10, 2006 at 6:43 pm
The HAVING clause is a filter AFTER the GROUP BY has been perfomed on the select statement. In other words, the filter affects the result of the aggregation only; and since you don't have a field called CurrentBalance in the aggregation (your SELET statement), the error was raised.
January 10, 2006 at 8:21 pm
thanks for that tip! and I have actually solved it so I no longer need assistance
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply