SQL: Correct use of the case statement?

user_2259783 0
Viewed 100

Why am I getting 0 for all the rows of this select? Some of the rows have values for all the variables in the statement, so it is incorrect. Syntax error? Order of operations?

I'm banging my head against the wall.

SELECT
      (isnull(VG.totalSales,0)/(case when ((isnull(IB.warehouseInventory,0)+isnull(IT.storesInventory,0)+isnull(VG.totalSales,0))=0) then 20 else (isnull(IB.warehouseInventory,0)+isnull(IT.storesInventory,0)+isnull(VG.totalSales,0))end*100)) as percentageSold
FROM dbo.myTable

UPDATE:

My problem has to do with 0/0, which SQL and math do not allowed.

Below is my complete SQL block.

The line I am having trouble with has been marked with a big comment block, please scroll down. This is the only column that is not working ok. All the other columns are working OK, so feel free to ignore them.

Pardon the change of language in the code; I translated my variables for my 'previous post' but it's too much for this one.

DECLARE @infosReferenciasGlobalPorExtension TABLE
    (
      referencia varchar(max),
      extension varchar(max),
      talla varchar(max),
      descripcion varchar(max),
      inventarioBodega int,
      vendidasGlobal int,
      inventarioTiendas int,
      fechaEntradaTiendas varchar(8),
      loteInicial int,
      porcentajeVendido float
    )
INSERT INTO @infosReferenciasGlobalPorExtension
SELECT
    R.referencia,
    R.extension,
    R.talla,
    R.descripcion,
    (isnull(IB.inventarioBodega,0)),
    (isnull(VG.vendidasGlobal,0)),
    (isnull(IT.inventarioTiendas,0)),
    FET.fechaEntradaTiendas,
    (isnull(IB.inventarioBodega,0)+isnull(IT.inventarioTiendas,0)+isnull(VG.vendidasGlobal,0)) as loteInicial,
    ----------
    --RIGHT BELOW THIS COMMENT IS THE LINE I HAVE TROUBLE WITH, BECAUSE 0/0 IS NOT ALLOWED. 
    --SOMETIMES THE THREE VALUES THAT MAKE UP THE DENOMINATOR ARE 0, LEADING TO A 0 DENOMINATOR.
    ----------
     ISNULL(VG.vendidasGlobal, 0) / (ISNULL(IB.inventarioBodega, 0)+ ISNULL(IT.inventarioTiendas, 0) + ISNULL(VG.vendidasGlobal, 0) ) AS porcentajeVendido
FROM @referencias as R
FULL OUTER JOIN @inventarioBodega as IB
    ON R.referencia=IB.referencia AND R.extension=IB.extension AND  R.talla=IB.talla
FULL OUTER JOIN @vendidasGlobal as VG
    ON R.referencia=VG.referencia AND R.extension=VG.extension AND  R.talla=VG.talla
FULL OUTER JOIN @inventarioTiendas as IT 
    ON R.referencia=IT.referencia AND R.extension=IT.extension AND  R.talla=IT.talla
FULL OUTER JOIN @fechaEntradaTiendas as FET 
    ON R.referencia=FET.referencia AND R.extension=FET.extension AND  R.talla=FET.talla
--si no tienen lote inicial, es una talla que nunca tiene, como XXL o xxs
WHERE (isnull(IB.inventarioBodega,0)+isnull(IT.inventarioTiendas,0)+isnull(VG.vendidasGlobal,0))>0
case tsql sql-server
3 Answers

As mentioned by others problem is integer division. When the numerator and denominator is integer then result will also be integer. example 

select 1/2

you would expect the result to be 0.5 but the result will be 0. To get the decimal part in result you need to convert either the numerator or denominator to be a decimal number. 

select 1/2.0 --or 1.0/2

will give you 0.5 as result. So in your case just make the * 100 as * 100.0 or multiply numerator or denominator with 1.0 to get the decimal part in result. Also your query can be simplified like this 

SELECT Isnull(VG.totalSales, 0) / (COALESCE(NULLIF(Isnull(IB.warehouseInventory, 0)
                                                  + Isnull(IT.storesInventory, 0)
                                                  + Isnull(VG.totalSales, 0), 0), 20) * 100.0) AS percentageSold
FROM   dbo.myTable

Update : To fix divide by zero exception use NULLIF in denominator 

ISNULL(VG.vendidasGlobal, 0) / 
        NULLIF((ISNULL(IB.inventarioBodega, 0)+ ISNULL(IT.inventarioTiendas, 0) + ISNULL(VG.vendidasGlobal, 0) ),0) AS porcentajeVendido
user_33495510

CAST your denominator or Numerator to Float/Decimal. 

SELECT ( ISNULL(VG.totalSales, 0)
         / CAST(( CASE WHEN (( ISNULL(IB.warehouseInventory, 0)
                               + ISNULL(IT.storesInventory, 0)
                               + ISNULL(VG.totalSales, 0)
                             ) = 0
                            ) THEN 20
                       ELSE
    ( ISNULL(IB.warehouseInventory, 0) + ISNULL(IT.storesInventory, 0)
      + ISNULL(VG.totalSales, 0)
    )
                  END * 100
                ) AS Float)
       ) AS percentageSold
FROM   dbo.myTable;
user_35135970

You can try it like this...

SELECT
    percentageSold = ISNULL(VG.totalSales, 0) / ISNULL(NULLIF(ISNULL(IB.warehouseInventory, 0)+ ISNULL(IT.storesInventory, 0)+ ISNULL(VG.totalSales, 0), 0), 20) * 100
FROM
    dbo.myTable;
user_82330790
Related