Concatenation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Concatenation problem

Author

Topic

chayolle
Starting Member

13 Posts

Posted - 2008-05-20 : 06:53:29

Hi, i have this query that is perfect on SQL2005 but in SQL2000 it gives me the error: "Column 'D.RevenueCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."


SELECT SUBSTRING(D.RevenueCode, 5, 2)  + '  ' + 
(SELECT A.sdp_des
 FROM vw_UNI_se_girev A
 WHERE A.sdp = SUBSTRING(D.RevenueCode, 5, 2)
 AND Cast(A.Comp AS Integer) = D.CompanyCode) AS RevenueGrpItem,

SUM(nSign * isNull(ItemAmount, 0)) AS GrandTotal

FROM SIC_Invoice_Header AS H,  vw_SIC_Invoice_Detail AS D
WHERE  H.CompanyCode     = 1
  AND  H.StockCategory   = 'INV'
  AND  H.CompanyCode     = D.CompanyCode 
  AND  H.StockCategory   = D.StockCategory
  AND  H.SaleType        = D.SaleType
  AND  H.TransactionType = D.TransactionType 
  AND  H.SerialNo        = D.SerialNo
  AND  D.RevenueCode is not NULL

GROUP BY D.CompanyCode, SUBSTRING(D.RevenueCode, 5, 2)
ORDER BY SUBSTRING(D.RevenueCode, 5, 2)