Solved

SQL query error+SQL Server

Posted on 2014-07-31
6
634 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 250 total points
ID: 40233451
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
ID: 40233460
Its simple view and still I am getting same error.
0
 
LVL 143

Assisted Solution

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

SUM(cast(MET as int))
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:sqldba2013
ID: 40233508
Met data type is decimal not bit.

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40233517
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
ID: 40245974
Thanks for your inputs. I have resolved my issue myself by modifying query.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

624 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