Need help in sql query - 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

General SQL Server Forums

New to SQL Server Programming

Need help in sql query

Author

Topic

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-06-13 : 04:18:13

Hi,

I am using the below query for calculation and I get this error.

Operand data type nvarchar is invalid for sum operator.

'$ '+ REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(sum(t7.[Pre Override Cost]),0),0) as money) + 1 -
cast(round(isnull(sum(t8.[Shared Dollars]),0),0)as money)),1)),4,255)) as [PreOverride L2],

'$ '+ REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(sum(t7.[Post Override Cost]),0),0) as money) -
cast(round(isnull(sum(t8.[Shared Dollars]),0),0)as money)),1)),4,255)) as [PostOverride L2]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 04:33:01

1. What on earth are you trying to do?
2. Which datatypes are [Pre Override Cost] and [Shared Dollars]?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-06-13 : 05:13:25

quote:
Originally posted by SwePeso

1. What on earth are you trying to do?
2. Which datatypes are [Pre Override Cost] and [Shared Dollars]?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Its money datatype

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 07:06:24

Seems shorter and easier

DECLARE	@Sample TABLE
	(
		[Pre Override Cost] MONEY NOT NULL,
		[Post Override Cost] MONEY NOT NULL,
		[Shared Dollars] MONEY NOT NULL
	);

INSERT	@Sample
	(
		[Pre Override Cost],
		[Post Override Cost],
		[Shared Dollars]
	)
VALUES	(
		150.34,
		2.77,
		75.22
	);

-- Old
SELECT	'$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(SUM([Pre Override Cost]),0),0) as money) + 1 - CAST(round(isnull(SUM([Shared Dollars]),0),0)as money)),1)),4,255)) as [PreOverride L2],
	'$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(SUM([Post Override Cost]),0),0) as money) - CAST(round(isnull(SUM([Shared Dollars]),0),0)as money)),1)),4,255)) as [PostOverride L2]
FROM	@Sample;

-- SwePeso
SELECT	'$ ' + CAST(CAST(CEILING(SUM([Pre Override Cost] - [Shared Dollars])) AS INT) AS VARCHAR(30)),
	'$ ' + CAST(CAST(CEILING(SUM([Post Override Cost] - [Shared Dollars])) AS INT) AS VARCHAR(30))
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-06-13 : 07:23:21

quote:

Originally posted by SwePeso

Seems shorter and easier

DECLARE	@Sample TABLE
	(
		[Pre Override Cost] MONEY NOT NULL,
		[Post Override Cost] MONEY NOT NULL,
		[Shared Dollars] MONEY NOT NULL
	);

INSERT	@Sample
	(
		[Pre Override Cost],
		[Post Override Cost],
		[Shared Dollars]
	)
VALUES	(
		150.34,
		2.77,
		75.22
	);

-- Old
SELECT	'$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(SUM([Pre Override Cost]),0),0) as money) + 1 - CAST(round(isnull(SUM([Shared Dollars]),0),0)as money)),1)),4,255)) as [PreOverride L2],
	'$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(SUM([Post Override Cost]),0),0) as money) - CAST(round(isnull(SUM([Shared Dollars]),0),0)as money)),1)),4,255)) as [PostOverride L2]
FROM	@Sample;

-- SwePeso
SELECT	'$ ' + CAST(CAST(CEILING(SUM([Pre Override Cost] - [Shared Dollars])) AS INT) AS VARCHAR(30)),
	'$ ' + CAST(CAST(CEILING(SUM([Post Override Cost] - [Shared Dollars])) AS INT) AS VARCHAR(30))
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I'm getting this error.
Operand data type nvarchar is invalid for sum operator.

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-06-13 : 07:23:34

quote:

Originally posted by SwePeso

Seems shorter and easier

DECLARE	@Sample TABLE
	(
		[Pre Override Cost] MONEY NOT NULL,
		[Post Override Cost] MONEY NOT NULL,
		[Shared Dollars] MONEY NOT NULL
	);

INSERT	@Sample
	(
		[Pre Override Cost],
		[Post Override Cost],
		[Shared Dollars]
	)
VALUES	(
		150.34,
		2.77,
		75.22
	);

-- Old
SELECT	'$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(SUM([Pre Override Cost]),0),0) as money) + 1 - CAST(round(isnull(SUM([Shared Dollars]),0),0)as money)),1)),4,255)) as [PreOverride L2],
	'$ ' + REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(SUM([Post Override Cost]),0),0) as money) - CAST(round(isnull(SUM([Shared Dollars]),0),0)as money)),1)),4,255)) as [PostOverride L2]
FROM	@Sample;

-- SwePeso
SELECT	'$ ' + CAST(CAST(CEILING(SUM([Pre Override Cost] - [Shared Dollars])) AS INT) AS VARCHAR(30)),
	'$ ' + CAST(CAST(CEILING(SUM([Post Override Cost] - [Shared Dollars])) AS INT) AS VARCHAR(30))
FROM	@Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

I'm getting this error.
Operand data type nvarchar is invalid for sum operator.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-13 : 14:14:29

I thought you said the datatype was MONEY?
Now you are telling me the datatype is nvarchar?

Why would anyone store monetary data in a nvarchar column?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources