Case Select with division and authorization

[Ras2019]

Hi,

I need a select statement which counts and divide based on conditions and authorization.  I run into a problem with formula not accepting division if there is a authorization involved.... it will accept Multiplication and Addition etc. but not if its division..

 

This statement is working (with Multiplication)

(SELECT COUNT(Quote_Used) from Quotes WHERE Quote_Used='Used' AND Tradelane_Owner = '[@authfield:Company_Users_tbl_Company_ID]') * (SELECT COUNT(Quote_Used) from Quotes WHERE Quote_Used='Expired' AND Tradelane_Owner = '[@authfield:Company_Users_tbl_Company_ID]')

Same statement (with Division) is not working i.e. not accepted in the formula

(SELECT COUNT(Quote_Used) from Quotes WHERE Quote_Used='Used' AND Tradelane_Owner = '[@authfield:Company_Users_tbl_Company_ID]') / (SELECT COUNT(Quote_Used) from Quotes WHERE Quote_Used='Expired' AND Tradelane_Owner = '[@authfield:Company_Users_tbl_Company_ID]')

Same Statement (with Division) but without authorization will work and is accepted in formula

(SELECT COUNT(Quote_Used) from Quotes WHERE Quote_Used='Used') / (SELECT COUNT(Quote_Used) from Quotes WHERE Quote_Used='Expired')

 

Appreciate if anyone have ideas of what can be the issue!

Sincerely

Ras

[Ras2019]

Hi,

Support gave the solution for the error message so sharing... 

Had to put a NULLIF in the second expression, that did it.

NULLIF((SELECT COUNT(Quote_Used) from Quotes WHERE Quote_Used='Expired' AND Tradelane_Owner = '[@authfield:Company_Users_tbl_Company_ID]'),0)

Cheers

Ras.