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 2005 Forums

Transact-SQL (2005)

Concatenation problem

Author

Topic

chayolle
Starting Member

13 Posts

Posted - 2008-05-20 : 07:08:47

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)

It appears that when I remove the + and put a comma it works correctly but that's not what I want !

Any suggestions?

Thanks in advance

Laurent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 07:24:11

May be this:-

SELECT t.RevenueCode + ' ' + t.RevenueGrpItem,t.GrandTotal
FROM
(SELECT SUBSTRING(D.RevenueCode, 5, 2) AS RevenueCode,
(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)t

GROUP BY t.CompanyCode,t.RevenueCode
ORDER BY t.RevenueCode

chayolle
Starting Member

13 Posts

Posted - 2008-05-20 : 08:02:52

Thanks but:

Msg 8120, Level 16, State 1, Line 1
Column 'vw_SIC_Invoice_Detail.RevenueCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 08:10:18

quote:
Originally posted by chayolle

Thanks but:

Msg 8120, Level 16, State 1, Line 1
Column 'vw_SIC_Invoice_Detail.RevenueCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

wat about this:-

SELECT t.RevenueCode + ' ' + t.RevenueGrpItem,
SUM(t.nSign * t.ItemAmount) AS GrandTotal
FROM
(SELECT SUBSTRING(D.RevenueCode, 5, 2) AS RevenueCode,
(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,
nSign,isNull(ItemAmount, 0) AS ItemAmount
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)t

GROUP BY t.CompanyCode,t.RevenueCode
ORDER BY t.RevenueCode

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 08:11:33

[code]SELECT SUBSTRING(D.RevenueCode, 5, 2) + ' ' + A.sdp_des AS RevenueGrpItem,
SUM(nSign * isNull(ItemAmount, 0)) AS GrandTotal
FROM SIC_Invoice_Header AS H
INNER JOIN vw_SIC_Invoice_Detail AS D ON D.CompanyCode = H.CompanyCode
AND D.StockCategory = H.StockCategory
AND D.SaleType = H.SaleType
AND D.TransactionType = H.TransactionType
AND D.SerialNo = H.SerialNo
AND D.RevenueCode is not NULL
LEFT JOIN vw_UNI_se_girev AS A ON A.sdp = SUBSTRING(D.RevenueCode, 5, 2)
AND Cast(A.Comp AS Integer) = D.CompanyCode
WHERE H.CompanyCode = 1
AND H.StockCategory = 'INV'
GROUP BY D.CompanyCode,
SUBSTRING(D.RevenueCode, 5, 2),
A.sdp_des
ORDER BY SUBSTRING(D.RevenueCode, 5, 2)[/code]

E 12°55'05.25"
N 56°04'39.16"

chayolle
Starting Member

13 Posts

Posted - 2008-05-20 : 08:32:20

Many thanks but still no good, getting crazy with this !!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 08:34:00

quote:
Originally posted by chayolle

Many thanks but still no good, getting crazy with this !!!!

why? what's error you're getting?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-20 : 08:34:07

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.25"
N 56°04'39.16"

chayolle
Starting Member

13 Posts

Posted - 2008-05-21 : 00:27:34

Msg 207, Level 16, State 3, Line 1
Invalid column name 'CompanyCode'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 01:20:51

quote:
Originally posted by chayolle

Msg 207, Level 16, State 3, Line 1
Invalid column name 'CompanyCode'.

Are you sure you have CompanyCode column in vw_SIC_Invoice_Detail

chayolle
Starting Member

13 Posts

Posted - 2008-05-21 : 02:09:42

Yes companyCode is in the view...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-21 : 02:13:29

quote:
Originally posted by chayolle

Yes companyCode is in the view...

and in SIC_Invoice_Header too? It would be more helpful if you can post the exact query used.

chayolle
Starting Member

13 Posts

Posted - 2008-05-21 : 03:09:30

Yes it's in SIC_Invoice_Header too. I used the same query that you gave me, i.e.


SELECT t.RevenueCode + ' ' + t.RevenueGrpItem,
SUM(t.nSign * t.ItemAmount) AS GrandTotal
FROM
(SELECT SUBSTRING(D.RevenueCode, 5, 2) AS RevenueCode,
(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,
nSign,isNull(ItemAmount, 0) AS ItemAmount
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)t

GROUP BY t.CompanyCode,t.RevenueCode
ORDER BY t.RevenueCode

chayolle
Starting Member

13 Posts

Posted - 2008-05-22 : 07:51:43

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 08:09:58

GROUP BY t.CompanyCode,t.RevenueCode, t.RevenueGrpItem

E 12°55'05.25"
N 56°04'39.16"

chayolle
Starting Member

13 Posts

Posted - 2008-05-23 : 00:56:12

Still Msg 207, Level 16, State 3, Line 1
Invalid column name 'CompanyCode'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 02:11:30

quote:
Originally posted by chayolle

Still Msg 207, Level 16, State 3, Line 1
Invalid column name 'CompanyCode'.

SELECT t.RevenueCode + ' ' + t.RevenueGrpItem,
SUM(t.nSign * t.ItemAmount) AS GrandTotal
FROM
(SELECT SUBSTRING(D.RevenueCode, 5, 2) AS RevenueCode,
(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,
nSign,isNull(ItemAmount, 0) AS ItemAmount,H.CompanyCode 
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)t

GROUP BY t.CompanyCode,t.RevenueCode
ORDER BY t.RevenueCode

You missed CompanyCode in derived table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources