Msg 8117, Level 16, State 1, Line 3

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

Transact-SQL (2008)

Msg 8117, Level 16, State 1, Line 3

Author

Topic

muhie
Starting Member

3 Posts

Posted - 2011-09-22 : 05:29:30

SELECT SalesArea, Branch, 'Region', 'Town', 'RepCode', 'Rep', 'Customer', ReportRep, 'Segment', Brand, Pack, 'Calyear', 'CalMonth', InvoiceDate, TradeDay,
'DayName', SUM (QtyInvoiced) AS QtyInvoiced, SUM('HLitre') AS HLitre
FROM (SELECT br.SalesArea, br.Branch, dbo.Proper(ac.RegionClass2) AS 'Region', dbo.Proper(ac.Location) AS 'Town', ac.Salesperson AS 'RepCode',
dbo.Proper(td.Name) AS 'Rep', dbo.Proper(ac.Name) AS 'Customer', td.ReportRep, dbo.Proper(ac.MicroSegment) AS 'Segment', ds.Brand,
ds.Pack, DATEPART(yy, a.InvoiceDate) AS 'Calyear', DATEPART(mm, a.InvoiceDate) AS 'CalMonth', a.InvoiceDate, b.TradeDay,
DATENAME(dw, a.InvoiceDate) AS 'DayName', a.QtyInvoiced, a.Volume AS 'HLitre'
FROM dbo.vArTrnDetail AS a WITH (nolock) INNER JOIN
dbo.Customers AS ac WITH (nolock) ON a.Customer = ac.Customer INNER JOIN
dbo.vBranch AS br WITH (nolock) ON ac.Branch = br.Branch INNER JOIN
dbo.DimStockCode AS ds WITH (nolock) ON a.StockCode = ds.StockCode LEFT OUTER JOIN
dbo.vSalesBudget AS b WITH (nolock) ON b.Area = ac.SalesTeam AND a.InvoiceDate = b.TradeDate LEFT OUTER JOIN
dbo.SalesReps AS td ON ac.Salesperson = td.Salesperson AND ac.Branch = td.Branch
UNION ALL
SELECT 'Distell' AS 'SalesArea', 'ZZ' AS 'Branch', dc.Region, dc.Town, dc.Rep AS 'RepCode', dc.Rep, dc.CustName AS 'Customer', b.ReportRep,
dc.Segment, ISNULL(ds.Brand, 'Not Captured') AS 'Brand', ISNULL(ds.Pack, 'Not Captured') AS 'Pack', a.CalYear, a.CalMonth, a.InvoiceDate,
td.TradeDay, DATENAME(dw, a.InvoiceDate) AS 'DayName', a.Cases, a.HLitres AS 'HL'
FROM dbo.DistellSales AS a INNER JOIN
dbo.DistellCustomers AS dc ON a.Customer = dc.CustCode INNER JOIN
dbo.DistellReps AS b ON dc.Rep = b.Rep LEFT OUTER JOIN
dbo.DimStockCode AS ds ON a.StockCode = ds.StockCode LEFT OUTER JOIN
(SELECT DISTINCT TradeDate, LEFT(DATENAME(dw, TradeDate), 3) AS 'dayName', TradeDay
FROM dbo.SalesBudget AS td) AS td ON a.InvoiceDate = td.TradeDate
WHERE (a.HLitres <> 0)) AS a
GROUP BY SalesArea, Branch, Region, Town, RepCode, Rep, Customer, ReportRep, Segment, Brand, Pack, Calyear, CalMonth, InvoiceDate, TradeDay,DayName

Error is:
Msg 8117, Level 16, State 1, Line 3
Operand data type varchar is invalid for sum operator.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 06:16:09

No need to post the same question twice.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165747

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources