Case Statment

Question

Case Statment

ahfb

SSC Enthusiast

Points: 165
More actions
November 30, 2014 at 11:07 am

#295508

Hi all,
I want to write a Query For Table
CASE WHEN ISNUMERIC(PriceColumn) = 1 THEN CAST(PriceColumn AS DECIMAL(18,2)) ELSE 0 END
Error:
Error converting data type nvarchar to numeric
-------------------------------------
Some of Data in PriceColumn:
null
0
300000
' '
--------------------------------------
What Can i do ?
Tnx For Ur Comment 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

You need to filter out the records first. The optimizer can do a LOT with your query (even sometimes things you think it shouldn't do). Try something like this (which is an example from my Common TSQL Mistakes SQL Saturday session):

Use tempdb

set nocount on

go

IF OBJECT_ID(N'Accounts', N'U') IS NOT NULL

DROP TABLE dbo.Accounts;

CREATE TABLE dbo.Accounts (

account_nbr INT NOT NULL PRIMARY KEY,

account_type VARCHAR(20) NOT NULL

CHECK (account_type IN ('Personal', 'Business Basic', 'Business Plus')),

account_reference VARCHAR(30) NOT NULL);

INSERT dbo.Accounts VALUES(1, 'Personal', 'abc');

INSERT dbo.Accounts VALUES(2, 'Business Basic', '101');

INSERT dbo.Accounts VALUES(3, 'Personal', 'def');

INSERT dbo.Accounts VALUES(4, 'Business Plus', '5');

SELECT account_nbr, account_reference AS account_ref_nbr

FROM dbo.Accounts

WHERE account_type LIKE 'Business%'

AND CAST(account_reference AS INT) > 20;

SELECT account_nbr, account_ref_nbr

FROM (SELECT account_nbr,

CAST(account_reference AS INT) AS account_ref_nbr

FROM dbo.Accounts

WHERE account_type LIKE 'Business%') AS A

WHERE account_ref_nbr > 20;

SELECT account_nbr, account_reference AS account_ref_nbr

FROM dbo.Accounts

WHERE account_type LIKE 'Business%'

AND CASE WHEN account_reference NOT LIKE '%[^0-9]%'

THEN CAST(account_reference AS INT)

END > 20;

--cleanup

Drop table dbo.Accounts

go

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 2

ahfb (11/30/2014)
Hi all,
I want to write a Query For Table
CASE WHEN ISNUMERIC(PriceColumn) = 1 THEN CAST(PriceColumn AS DECIMAL(18,2)) ELSE 0 END
Error:
Error converting data type nvarchar to numeric
-------------------------------------
Some of Data in PriceColumn:
null
0
300000
' '
--------------------------------------
What Can i do ?
Tnx For Ur Comment 🙂

ISNUMERIC should not be confused with ISALLDIGITS or even ISADECIMAL. Please see the following article for why.http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)