Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Conditional Case Construct in SQL

Posted on 2009-02-17
10
Medium Priority
?
407 Views
Last Modified: 2012-05-06
I am attempting to use a CASE construct to cast a column value as a decimal after checking to see if it is numeric.  

Syntax is similar to Case isnumeric(tabval)= 1 and .. then cast(tabval) as decimal(19,4) else CASE when isnumeric(tabValue)=0 then tabvalue end

Obtain Error converting data type varchar to numeric error message when tabvalue is not numeric.

I have tried several variations of the statement in the code samples, but all result with the same error.
0
Comment
Question by:mjvsk1
  • 5
  • 4
10 Comments
 

Author Comment

by:mjvsk1
ID: 23663458

TabValueDim is defined as varchar(2048)
 
select  top 2 tabvaluedimid,tabValue,  
CASE when isnumeric(tabValue)=1 and tabvalue <> ''  and tabValue NOT LIKE '%[^.0123456789]%' then 'numeric' else 'not numeric' end   as Test_Result
--,CASE when isnumeric(tabValue)=1 and tabvalue <> ''  and tabValue NOT LIKE '%[^.0123456789]%'  then cast(substring(tabalue,1,14) as decimal(19,4)) else tabvalue end end as test_result
from  [dbo].[TabValueDim] 
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID
 
/*
tabvaluedimid	tabValue	        Test_Result
26655		100.4			numeric
26656		100.4 at home	        not numeric
 
(2 row(s) affected)
 
*/
 
 
select  top 2 tabsvaluedimid,tabValue,  
CASE when isnumeric(tabValue)=1 and tabvalue <> '' and tabValue NOT LIKE '%[^.0123456789]%' then 'numeric' else 'not numeric' end   as Test_Result
, CASE when isnumeric(tabValue)=1 and tabvalue <> '.' and tabvalue <> '' and tabalue NOT LIKE '%[^.0123456789]%'  then cast(substring(tabvalue,1,14) as decimal(19,4)) else CASE when isnumeric(tabValue)=0 then tabvalue end end as test_result
from [dbo].[TabValueDim] 
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID
 
/*
tabvaluedimid	        tabValue                test_result
26655			100.4			numeric			100.4000
 
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
 
 
*/
 
select  top 2 tabsvaluedimid,tabValue,  
CASE when isnumeric(tabValue)=1 and tabvalue <> '' and tabValue NOT LIKE '%[^.0123456789]%' then 'numeric' else 'not numeric' end   as Test_Result
, CASE when isnumeric(tabValue)=1 and tabvalue <> '.' and tabvalue <> '' and 
tabvalue NOT LIKE '%[^.0123456789]%'  then cast(substring(tabvalue,1,14) as decimal(19,4)) else tabvalue end  
from [dbo].[TabValueDim] 
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID
 
 
/*
tabvaluedimid	tabValue		test_result
26655		100.4			numeric			100.4000
 
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
 
 
*/

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 23663490
What is your SQL statement. did you try like this?
CASE ISNUMERIC(tabvalue) WHEN 0 THEN tabvalue ELSE CONVERT(DECIMAL(19,4),tabvalue) END

Open in new window

0
 

Author Comment

by:mjvsk1
ID: 23663697

I got the same result (even though it looks like it should work)

select  top 2 tabvaluedimid,tabValue,  isnumeric(tabValue) as 'IsNumeric_ReturnValue'
from  dbo].[tabValueDim]
where tabvaluedimid in (26655 ,26656)
order by   tabValueDimID

/*
tabvaluedimid      tabValue            IsNumeric_ReturnValue
26655            100.4                  1
26656            100.4 at home            0

(2 row(s) affected)
*/

select  top 2 tabvaluedimid,tabValue, isnumeric(tabValue) as 'IsNumeric_ReturnValue',
CASE ISNUMERIC(tabvalue) WHEN 0 THEN tabvalue ELSE CONVERT(DECIMAL(19,4),tabvalue) END
 from  [dbo].[tabValueDim]
where tabvaluedimid in (26655 ,26656)
order by   tabValueDimID

/*
tabvaluedimid      tabValue      IsNumeric_ReturnValue      
26655              100.4          1                      100.4000
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

*/
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:Sharath
ID: 23663711
try this.
select top 2 tabsvaluedimid,tabValue,  
       CASE when isnumeric(tabValue)=1 then 'numeric' else 'not numeric' end   as Test_Result
       ,CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabalue) else convert(decimal(19,4),substring(tabValue,1,charindex(' ',tabValue))) end end as test_result
from [dbo].[TabValueDim] 
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 23663738
some syntax error in previous post
select top 2 tabsvaluedimid,tabValue,  
       CASE when isnumeric(tabValue)=1 then 'numeric' else 'not numeric' end   as Test_Result
       ,CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabalue) 
             else convert(decimal(19,4),substring(tabValue,1,charindex(' ',tabValue))) end as test_result
from [dbo].[TabValueDim] 
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID

Open in new window

0
 

Author Comment

by:mjvsk1
ID: 23664949
I was trying to use the CASE statement in a dynamic query similar to

 
declare @searchvalue varchar(2048)

declare @sqlstatement nvarchar(max)
declare @searchOperator varchar(20)
set @searchvalue = '100.4'
set @searchOperator = '>'
 
set @sqlstatement =''
set @sqlstatement = 'select top 2 tabvaluedimid,tabValue,  
       CASE when isnumeric(tabValue)=1 then ''numeric'' else ''not numeric'' end   as Test_Result
       ,CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabvalue)  
             else  cast(tabValue as varchar(2058)) end '+@SearchOperator+' '+@SearchValue+'
  from [dbo].[tabValueDim]
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID'

print @sqlstatement

exec sp_executesql @sqlStatement

generated SQL with execution errors:

select top 2 obsvaluedimid,obsValue,  
       CASE when isnumeric(obsValue)=1 then 'numeric' else 'not numeric' end   as Test_Result
       ,CASE when isnumeric(obsValue)=1 then convert(decimal(19,4),obsvalue)  
             else  cast(obsValue as varchar(2058)) end > 100.4
  from [dbo].[scaobsValueDim]
where obsvaluedimid in (26655, 26656)
order by   obsValueDimID
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23665318
are you looking for this?
declare @searchvalue varchar(2048)
 
declare @sqlstatement nvarchar(max)
declare @searchOperator varchar(20)
set @searchvalue = '100.4'
set @searchOperator = '>' 
 
set @sqlstatement =''
set @sqlstatement = 
'select top 2 tabsvaluedimid,
       tabValue,  
       CASE when isnumeric(tabValue)=1 then ''numeric'' else ''not numeric'' end   as Test_Result
       ,CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabalue) 
             else convert(decimal(19,4),substring(tabValue,1,charindex('' '',tabValue))) end
        ,case when (CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabalue) 
             else convert(decimal(19,4),substring(tabValue,1,charindex('' '',tabValue))) end) ' + @SearchOperator + ' ' + @SearchValue +
' then ''Greater than'' else ''Less than'' end' +  
' from [dbo].[TabValueDim] 
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID'
 
print @sqlstatement

Open in new window

0
 

Author Comment

by:mjvsk1
ID: 23665524
The above query is pretty close.

the problem I'm having is the assignment of the variable that depends upon whether the tabvalue is numeric or not (the field is populated with numeric values as well as any type of text).

If the tabValue column is not numeric, I'm trying to have the SQL use the tabValue column in the assignment statement:

CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabvalue)
else  use tabValue  end '+@SearchOperator+' '+@SearchValue+'

(I don't think using tabvalue in the else condition is allowed)
The @searchOperator and @searchvalue variables are being selected from a database table and the content of the variables varies (@searchOperator value set is in (<,>,=,<=,>= In, not in) and @searchvalue can be a list or individual alphanumeric or numeric values.

 
declare @searchvalue varchar(2048)

declare @sqlstatement nvarchar(max)
declare @searchOperator varchar(20)
set @searchvalue = '100.4'
set @searchOperator = '>'
 
set @sqlstatement =''
set @sqlstatement = 'select top 2 tabvaluedimid,tabValue,  
       CASE when isnumeric(tabValue)=1 then ''numeric'' else ''not numeric'' end   as Test_Result
       ,CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabvalue)  
             else  cast(tabValue as varchar(2058)) end '+@SearchOperator+' '+@SearchValue+'
  from [dbo].[tabValueDim]
where tabvaluedimid in (26655, 26656)
order by   tabValueDimID'

print @sqlstatement
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 23665597

>> If the tabValue column is not numeric, I'm trying to have the SQL use the tabValue column in the assignment statement:
I don't understand this statement. writing dynamic sql is not a problem. first can you explain what do you want exactly. if possible with an example.
If it not numeric, do you want to extract the numerical part and comapre tablValue with @SearchValue? Do you want to display "greater than" or " less than" after comparing. Provide the expected result.
0
 
LVL 6

Assisted Solution

by:FVER
FVER earned 500 total points
ID: 23669039
I think you have now understood that all the values for a column must be of a single datatype.
That is why this statement
CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabvalue)  
             else  cast(tabValue as varchar(2058)) end
is forbidden.

I hope that the query below can fill your need. Please note that in case of a vachar comparison, you should enclose the value in quotes.
set @sqlstatement =''
set @sqlstatement = 'select top 2 tabvaluedimid,tabValue,  
       CASE when isnumeric(tabValue)=1 then ''numeric'' else ''not numeric'' end   as Test_Result
      ,CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabvalue) else NULL end tabValue_num
  from [dbo].[tabValueDim]
where tabvaluedimid in (26655, 26656)
  and (   (tabvalue '+@SearchOperator+' '''+@SearchValue+'''
            and isnumeric(tabValue)=0)
       or CASE when isnumeric(tabValue)=1 then convert(decimal(19,4),tabvalue) else NULL end '+@SearchOperator+' '+@SearchValue+'
      )
order by   tabValueDimID'
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 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