Conditional Case Construct in SQL

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.
mjvsk1Asked:
Who is Participating?
 
SharathData EngineerCommented:

>> 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
 
mjvsk1Author Commented:

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
 
SharathData EngineerCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
mjvsk1Author Commented:

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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
mjvsk1Author Commented:
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
 
SharathData EngineerCommented:
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
 
mjvsk1Author Commented:
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
 
FVERCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.