Jump to content
  • 0

Case Select with division and authorization


Ras2019
 Share

Question

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

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

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.

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...