Help with Case :-( - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Case :-(

Author

Topic

Jimbojames30
Starting Member

8 Posts

Posted - 2015-03-17 : 09:17:08

Hi All,

please can some one help me out, not great at sql and need to Help from you guru's

i have a case statment that i want to check if the record exsits in another table to say 'Recent' else do a calculation but o get the Error "converting data type varchar to numeric. Warning: Null value is eliminated by an aggregate or other SET operation."

please could someone point me out what im doing wrong please

 CASE WHEN  (SELECT COUNT(ab.PrecentRecent) FROM DPY_SyncReport_Prod.Consignment.JointsRecentSummary As ab
							where A.ZtypeNumber = AB.ZtypeNumber 
							and A.ProductHealthCategory = AB.ProductHealthCategory
							and A.ProductHealthBrand = AB.ProductHealthBrand) > 0 then 'Recent'
							
			WHEN CAST(SUM(a.TotaBasePrice) / NULLIF((ISNULL(SUM(b.SalesVale12Mth),0) / 365),0) AS Numeric(8,0))  IS NULL  THEN 99999
ELSE CAST(SUM(a.TotaBasePrice) / NULLIF((ISNULL(SUM(b.SalesVale12Mth),0) / 365),0)  AS Numeric (8,0))
END As DSI

Ifor
Aged Yak Warrior

700 Posts

Posted - 2015-03-17 : 09:41:26

The result of a CASE can only have one datatype:


CASE
	WHEN (SELECT ...)
	THEN CAST('Recent' AS varchar(8))
	WHEN SUM(b.SalesVale12Mth) IS NULL
	THEN CAST('99999' AS varchar(8))
	ELSE CAST(CAST(SUM(a.TotaBasePrice) / (SUM(b.SalesVale12Mth) / 365) AS numeric(8,0)) AS varchar(8))
END AS DSI

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-18 : 03:44:45

[code]CASE
WHEN EXISTS(SELECT * FROM DPY_SyncReport_Prod.Consignment.JointsRecentSummary As ab where A.ZtypeNumber = AB.ZtypeNumber and A.ProductHealthCategory = AB.ProductHealthCategory and A.ProductHealthBrand = AB.ProductHealthBrand) then 'Recent'
WHEN SUM(b.SalesVale12Mth) = 0 THEN '99999'
ELSE CAST(SUM(a.TotaBasePrice) / NULLIF((ISNULL(SUM(b.SalesVale12Mth), 0) / 365),0) AS VARCHAR(20)
END AS DSI[/code]

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