((PO.Quantity)/((EIN.TIER_QTY)*(EIN.TIE_QTY))) AS [PalletConfig]
I am using in my select statement on SQL server but getting below errot. Can anybody fix it please.
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
((PO.Quantity)/((EIN.TIER_QTY)*(EIN.TIE_QTY))) AS [PalletConfig]
I am using in my select statement on SQL server but getting below errot. Can anybody fix it please.
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
How about:
((PO.Quantity)/(Nz((EIN.TIER_QTY),1)*Nz((EIN.TIE_QTY),1))) AS [PalletConfig]
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
Adding on to Bob's answer,
If [EIN.TIER_QTY] can actually be zero, I would try
Code:((PO.Quantity)/IIF(Nz((EIN.TIER_QTY),1)=0,1,(Nz((EIN.TIER_QTY),1)*Nz((EIN.TIE_QTY),1)))) AS [PalletConfig]
removed post - needs more thought
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Even simple iff statement is not working on my SQL management studio.
DECLARE @Genderid INT
SET @Genderid = 1
SELECT IIF(@Genderid =1,'MALE', 'FEMALE') as Gender
error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
I'm having some luck with
POQuantity / IIf(Nz(EINTIER_QTY, 1) = 1, 1, EINTIER_QTY) * IIf(Nz(EINTIE_QTY, 1) = 1, 1, EINTIE_QTY)
(I had to alter the table/field references to variable names I could assign values to since I don't have the data).
If POQuantity = 100; EINTIER_QTY = 20; EINTIE_QTY = 5
I get 25 as a result. If I make either or both EINTIER_QTY EINTIE_QTY Null I get varying answers. Not sure if that fits the bill.
Not sure you should be arbitrarily computing values based on Nulls or zeros anyway. The best solution might be to ensure that table fields do not default to zero and cannot accept nulls, or code for the error and present a message that one of the values is missing or zero.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
I missed that too. Hope my structure provides some benefit anyway, as I came up with a different structure in the nested functions.
Then again, there may be db design issues if values required for a calculation are allowed to have nulls or zeros. Maybe, maybe not.