CODEWITHSUNDEEP
sqlcase
user3731385
user3731385

Reputation: 3

SQL Server : Then executed when When is false?

I have the following case statement

case 
   when [cross].WEIGHT is not null and len([cross].WEIGHT) > 0
      then [cross].WEIGHT
   when pricesvc.INDV_WEIGHT is not null and len(pricesvc.INDV_WEIGHT) > 0
      then pricesvc.INDV_WEIGHT
   when pricesvc.PKG_WEIGHT is not null and len(pricesvc.PKG_WEIGHT) > 0 and pricesvc.PS_LGCY_QTY3 is not null and len(pricesvc.PS_LGCY_QTY3) > 1 and right(pricesvc.PS_LGCY_QTY3,1) = 'M'
      then CONVERT(FLOAT, REPLACE([pricesvc].[PKG_WEIGHT],',','')) / (CONVERT(FLOAT, REPLACE(left([pricesvc].[PS_LGCY_QTY3],len([pricesvc].[PS_LGCY_QTY3])-1),',','')) * 10000)
   when pricesvc.PKG_WEIGHT is not null and len(pricesvc.PKG_WEIGHT) > 0 and pricesvc.PS_LGCY_QTY3 is not null and len(pricesvc.PS_LGCY_QTY3) > 0 
      then CONVERT(FLOAT, REPLACE([pricesvc].[PKG_WEIGHT],',','')) / CONVERT(FLOAT, REPLACE([pricesvc].[PS_LGCY_QTY3],',',''))
   when pricesvc.PKG_WEIGHT is not null and len(pricesvc.PKG_WEIGHT) > 0 
      then pricesvc.PKG_WEIGHT
   else '0.01'
end as 'weight',

The very first when is true, however the third when/then is still executed and throws an error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

I added the When test for null and zero length but the then convert is still executed and gives error. Seems strange, as I thought a false condition would not be executed

Upvotes: 0

Views: 69

Answers (1)

TMNT2014
TMNT2014

Reputation: 2130

In your Else clause have 0.01 instead of the varchar '0.01' and see if that helps. Additionally what are the datatypes for [cross].WEIGHT and pricesvc.INDV_WEIGHT and pricesvc.PKG_WEIGHT? You would need to cast them as float too. Essentially the error is not because the third condition is being executed its because your SQL failed to parse due to the fact that you have different datatypes in the results of your case statement that cannot be implicitly converted.

Try This - 

case 
   when [cross].WEIGHT is not null and len([cross].WEIGHT) > 0
      then CONVERT(FLOAT, REPLACE([cross].WEIGHT,',',''))
   when pricesvc.INDV_WEIGHT is not null and len(pricesvc.INDV_WEIGHT) > 0
      then then CONVERT(FLOAT, REPLACE(pricesvc.INDV_WEIGHT,',','')) 
   when pricesvc.PKG_WEIGHT is not null and len(pricesvc.PKG_WEIGHT) > 0 and pricesvc.PS_LGCY_QTY3 is not null and len(pricesvc.PS_LGCY_QTY3) > 1 and right(pricesvc.PS_LGCY_QTY3,1) = 'M'
      then CONVERT(FLOAT, REPLACE([pricesvc].[PKG_WEIGHT],',','')) / (CONVERT(FLOAT, REPLACE(left([pricesvc].[PS_LGCY_QTY3],len([pricesvc].[PS_LGCY_QTY3])-1),',','')) * 10000)
   when pricesvc.PKG_WEIGHT is not null and len(pricesvc.PKG_WEIGHT) > 0 and pricesvc.PS_LGCY_QTY3 is not null and len(pricesvc.PS_LGCY_QTY3) > 0 
      then CONVERT(FLOAT, REPLACE([pricesvc].[PKG_WEIGHT],',','')) / CONVERT(FLOAT, REPLACE([pricesvc].[PS_LGCY_QTY3],',',''))
   when pricesvc.PKG_WEIGHT is not null and len(pricesvc.PKG_WEIGHT) > 0 
      then pricesvc.PKG_WEIGHT
   else 0.01
end as 'weight',`

Upvotes: 1

Related Questions