Case Statment

Question

Post reply

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: 1000539 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://qa.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)
Intro to Tally Tables and Functions