Solved

SQL query error+SQL Server

Posted on 2014-07-31
6
613 Views
Last Modified: 2014-08-07
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

0
Comment
Question by:sqldba2013
  • 3
  • 2
6 Comments
 
LVL 14

Accepted Solution

by:
Vikas Garg earned 250 total points
Comment Utility
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
 

Author Comment

by:sqldba2013
Comment Utility
Its simple view and still I am getting same error.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
assuming that MET is the bit field, you need to do:

SUM(cast(MET as int))
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:sqldba2013
Comment Utility
Met data type is decimal not bit.

View structure:
Year      int
Month      int
[Met]      decimal
[amount]      int
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Closing Comment

by:sqldba2013
Comment Utility
Thanks for your inputs. I have resolved my issue myself by modifying query.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now