Click here to Skip to main content
15,880,725 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I work on SQL server 2012 I face issue on code below when

EStrat value is

number as sample >1000 or <1000 or =1000 as sample it working good

because it will use AcceptedValuesOption_Value and this field is integer so no issue on that

issue happen when EStrat value is <>'Solid State Relay' or ='Solid State Relay'

so it will use field AcceptedValuesOption_Value and this field is integer so it is wrong and it will stuck

correct is when EStrat value is <>'Solid State Relay' or ='Solid State Relay' use Name

so How to correct statement below to do that

SQL
SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' +  case when PATINDEX('%[><=]%', EStrat)>0  then 
    'AcceptedValuesOption_Value ' 
    when EStrat is null then
    'Name '
    else 
    'Name '
    end  ) , case when PATINDEX('%[><=]%', EStrat)>0 then
 CAST(EStrat AS NVARCHAR(2500))
 when EStrat is null then
 ' is null '
 else
 ''+CAST(EStrat AS NVARCHAR(2500)) +''
 end ,')')


What I have tried:

SQL
SET @ConStr=  STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' ,' And ' +  case when PATINDEX('%[><=]%', EStrat)>0 and isnumeric(EStrat)=0 then
Posted
Updated 3-Jun-21 3:14am

1 solution

Not sure if I understand the situation correctly, but if the database is interpreting the type of the column as int based on the first possible return value, you can force it to varchar in order to match other possible values.

So try changing AcceptedValuesOption_Value to
SQL
cast(AcceptedValuesOption_Value as varchar)
 
Share this answer
 
Comments
ahmed_sa 3-Jun-21 9:24am    
case when after <> or = is string then use Name else using AcceptedValuesOption_Value
so How to do that please
Wendelius 3-Jun-21 9:33am    
Based on that short excerpt I'm not able to say what should be done. Please post the whole SQL statement causing the problem.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900