BAQ Calculated Field error

Epicor ERP 10
1

So here is what I am trying to do:

case when PartPlant.MMSHistory = 0 then ceiling (case when (sum(orderdtl.SellingQuantity/12) < 3 or sum(orderdtl.SellingQuantity) is null then 6 else (sum(orderdtl.SellingQuantity)/12) end)
else ceiling (case when (sum(orderdtl.SellingQuantity)/12) < 3 or sum(orderdtl.SellingQuantity) is null then 3 else (sum(orderdtl.SellingQuantity)/12) end

And when i hit check syntax it says: Incorrect syntax near the keyword ‘then’ Except nothing looks out of place. Since I’m going cross eyed, I thought I’d see if a fresh pair of eyes would help me.

2

Ok, found my error for this and it is now

case when PartPlant.MMSHistory = 0 then ceiling (case when (sum(orderdtl.SellingQuantity)/12) < 3 or sum(orderdtl.SellingQuantity) is null then 6 else (sum(orderdtl.SellingQuantity)/12) end)
else ceiling (case when (sum(OrderDtl.sellingSellingQuantity)/12) < 3 or sum(OrderDtl.SellingQuantity) is null then 3 else (sum(OrderDtl.SellingQuantity)/12) end

But It now says Incorrect Syntax near ‘Calcualted_SuggetedMin.’

3

You have no closing parens on this one.

image
image.png1026×95 7.68 KB

1 Like
4

And here is the winner!
One paren and another End.

case when PartPlant.MMSHistory = 0 then ceiling (case when (sum(orderdtl.SellingQuantity)/12) < 3 or sum(orderdtl.SellingQuantity) is null then 6 else (sum(orderdtl.SellingQuantity)/12) end)
else ceiling (case when (sum(OrderDtl.SellingQuantity)/12) < 3 or (sum(OrderDtl.SellingQuantity)/12) is null then 3 else (sum(OrderDtl.SellingQuantity)/12) end) end

5

nesting conditions is fun isn’t it?

1 Like
6

Like pulling teeth fun! LOL!

7

When I get stuck with these, I find the best way to copy into SSMS (or notepad++ and set the language to SQL) and then reformat it like the following as it’s easy to figure out what’s missing:

-------------------------------
case when PartPlant.MMSHistory = 0 
then ceiling 
  (
    case when (sum(orderdtl.SellingQuantity/12) < 3 
      or sum(orderdtl.SellingQuantity) is null 
	then 6 
    else (sum(orderdtl.SellingQuantity)/12) 
	end
  )
else ceiling 
  (
    case when (sum(orderdtl.SellingQuantity)/12) < 3 
	  or sum(orderdtl.SellingQuantity) is null 
	then 3 
	else (sum(orderdtl.SellingQuantity)/12) 
end
-------------------------------

From here it’s easy to see that there is a missing a closing parentheses in the second case and missing end and closing parentheses to the last case.

1 Like
8

I’ll try that next time. Sounds much better than what I was doing!