We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Conditional Case Construct in SQL

mjvsk1
mjvsk1 asked
on
Medium Priority
450 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.
Comment
Watch Question

Author

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

SharathData Engineer
CERTIFIED EXPERT

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

Author

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.

*/
SharathData Engineer
CERTIFIED EXPERT

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

SharathData Engineer
CERTIFIED EXPERT

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

Author

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
SharathData Engineer
CERTIFIED EXPERT

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

Author

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
Data Engineer
CERTIFIED EXPERT
Commented:

>> 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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
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'
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.