Matthew Jakachira Matthew Jakachira - 3 months ago 13
SQL Question

Formula in case statement returning zero

I have a really weird issue with this case statement and formula where I am getting 0 for this division formula even though if I run each statement independently I get the correct number. If I delete the second selet statement and even enter the number in I still get zero. The numbers come out to 167913/1150972, if I truncate the second number to 1150 it works but if I add in the 9 I get 0 returned.

CASE WHEN
ISNULL(MAX(JC.JOB_SHIPPED_FLAG), MAX(OE10.ORD_SHIPPED_FLAG)) = 'P' AND ISNULL(MAX(JC.JOB_STATUS_FLAG), MAX(OE10.ORD_STATUS_FLAG)) = 2
THEN (SELECT SUM(CAST(TOT_COST AS INT)) FROM CRCDATA.DBO.LA15
WHERE EST_NUMB = MAX(JC.EST_NUMB) AND LA15.QTY_SLOT =1)
/
((SELECT DISTINCT SUM(CAST(FG14.ONHAND_QTY AS INT)) FROM CRCDATA.DBO.JC11 JC11
LEFT OUTER JOIN CRCDATA.DBO.FG14 FG14 ON JC11.FG_ITEM_NUMB = FG14.ITEM_NUMB
WHERE JC11.FG_ITEM_NUMB LIKE '0000000%' AND JC11.STATUS_FLAG = 'P' AND FG14.LOC <> 'rej' ) +
(SELECT SUM(CAST(SHIPTO_FAX AS INT)) FROM CRCDATA.DBO.SH10 WHERE RECORD_TYPE = 'B' AND PROCESS_FLAG = 4 AND SH10.JOB_NUMB = A.JobNumber))


ELSE 0
END [TEST]

Answer

You will have to cast any one of the divider or dividend to FLOAT. i.e.

SELECT CAST(val1 AS FLOAT) / val2

OR

SELECT val1 / CAST(val2 AS FLOAT)
Comments