SQL query error+SQL Server

Hi All,

Please suggest how to fix below error.

Error:
Msg 8117, Level 16, State 1, Procedure Employee_vw, Line 3
Operand data type bit is invalid for sum operator.
Msg 4413, Level 16, State 1, Line 8
Could not use view or function 'Employee_vw' because of binding errors.
select
concat(year,'-',(select left(datename(month,dateadd(month, month , -1)),3))) [Date],
sum(amount) [amount],
sum
(
[Met]
)
[Order] from Employee_vw
group by year,month
order by year,month

View structure:
Year	int
Month	int
[Met]	decimal
[amount]	int

Open in new window

sqldba2013Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vikas GargConnect With a Mentor Business Intelligence DeveloperCommented:
Hi,

have you used cross database reference in the View.

Since if it is a simple View then it will work perfectly as I have tested it locally and it is working fine..

CREATE VIEW [dbo].[Employee_VW]
AS
SELECT DISTINCT YEAR(FullDateAlternateKey) AS YEAR, MONTH(FullDateAlternateKey) AS MONTH, YEAR(FullDateAlternateKey) * 2 AS AMOUNT, MONTH(FullDateAlternateKey) * 2.5 AS MET
FROM            [AdventureWorksDW2012].[dbo].[DimDate]


GO

select
concat(year,'-',(select left(datename(month,dateadd(month, month , -1)),3))) [Date],
sum(amount) [amount],
sum
(
[Met]
)
[Order] from [dbo].[Employee_VW]
group by year,month
order by year,month

Open in new window

0
 
sqldba2013Author Commented:
Its simple view and still I am getting same error.
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
assuming that MET is the bit field, you need to do:

SUM(cast(MET as int))
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sqldba2013Author Commented:
Met data type is decimal not bit.

View structure:
Year      int
Month      int
[Met]      decimal
[amount]      int
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the issue is defintively then in the view Employee_vw, please post that full query

select concat(year,'-', left(datename(month,dateadd(month, month , -1)),3) ) [Date]
, sum(amount) [amount]
, sum([Met]) [Order] 
from Employee_vw
group by year,month
order by year,month

Open in new window

0
 
sqldba2013Author Commented:
Thanks for your inputs. I have resolved my issue myself by modifying query.
0
All Courses

From novice to tech pro — start learning today.