none
SQLSERVER BUG on CASE WHEN STATEMENT RRS feed

  • Question

  • select left(v.kode_barang,6)as kode_barang,nama_barang,kode_gudang,kode_wilayah,
    v.qty,u.qty_a,
    case  when (v.qty >=0) then 
        case when (u.qty_a=1) then v.qty else
      case   when  (v.qty>=u.qty_a) and (u.qty_a<>1) then 
        floor(v.qty/u.qty_a) else 0 end end     
              when v.qty<0 then
                      case when  v.qty*(-1) >=u.qty_a then floor(v.qty*(-1)/u.qty_a)*(-1) else 0 end      
        end as QTY_A,

    when V.qty is 82.70 and u.qty_a is 1. why result is 83 ?????

     
    Monday, March 26, 2018 3:43 PM

All replies

  • It works for me. You can combine the second and the third CASE statement together:

    DECLARE @d decimal(5, 2) = 82.70;
    DECLARE @i int = 1;
    SELECT @d, @i;
    SELECT 
    	CASE 
    		WHEN @d >= 0 THEN 
    			CASE 
    				WHEN @i = 1 THEN @d 
    				WHEN @d >= @i THEN FLOOR(@d/@i) 
    				ELSE 0 
    			END
    		WHEN @d < 0 THEN 
    			CASE 
    				WHEN @d * (-1) >= @i THEN FLOOR(@d * (-1) / @i) * (-1) 
    				ELSE 0 
    			END	
    	END


    A Fan of SSIS, SSRS and SSAS

    Monday, March 26, 2018 4:29 PM
  • i guess thats because you're returning an integer in the other condition. So its getting rounded

    try like

    select left(v.kode_barang,6)as kode_barang,nama_barang,kode_gudang,kode_wilayah,
    v.qty,u.qty_a,
    case  when (v.qty >=0) then 
        case when (u.qty_a=1) then v.qty else
      case   when  (v.qty>=u.qty_a) and (u.qty_a<>1) then 
        floor(v.qty/u.qty_a) else 0.00 end
    end     
              when v.qty<0 then
                      case when  v.qty*(-1) >=u.qty_a then floor(v.qty*(-1)/u.qty_a)*(-1) else 0.00 end      
        end as QTY_A,


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Tom Phillips Monday, March 26, 2018 5:24 PM
    Monday, March 26, 2018 4:33 PM
  • What data type is u.qty_a? If it is float, it may only seem that is 1, but there may be hidden decimals, which you will see if you subtract 1 from it. In this case, it seems that you end up in floor(v.qty / u.qty_a) which would be 83.

    When you work with float, you always need a fuzzy margin. I would write the condition as:

       abs(u.qty_a - 1) < 1E-10

    Same goes for the inequality later.

    (The choise of -10 for the exponent is somewhat arbitrary.)

    Monday, March 26, 2018 9:36 PM