BAQ: Case expressions may only be nested to level 10

Epicor ERP 10
,
1

Hi, I am trying to write a report for our GL account budgets, broken down by monthly amounts. We soft close 52 periods on a 4-4-5 schedule. So my budgets are loaded by periods, and I’m trying to calculate the month from the period. Here is the nested case expression i’m using, which should work great, but I get the error: “Case expressions may only be nested to level 10.”

Anyone have an idea of another way I could conclude the month from the period?

(
  case when (
    1 <= GLBudgetDtl.FiscalPeriod 
    and GLBudgetDtl.FiscalPeriod <= 4
  ) then 1 else (
    case when (
      5 <= GLBudgetDtl.FiscalPeriod 
      and GLBudgetDtl.FiscalPeriod <= 8
    ) then 2 else (
      case when (
        9 <= GLBudgetDtl.FiscalPeriod 
        and GLBudgetDtl.FiscalPeriod <= 13
      ) then 3 else (
        case when (
          14 <= GLBudgetDtl.FiscalPeriod 
          and GLBudgetDtl.FiscalPeriod <= 17
        ) then 4 else (
          case when (
            18 <= GLBudgetDtl.FiscalPeriod 
            and GLBudgetDtl.FiscalPeriod <= 21
          ) then 5 else (
            case when (
              22 <= GLBudgetDtl.FiscalPeriod 
              and GLBudgetDtl.FiscalPeriod <= 26
            ) then 6 else (
              case when (
                27 <= GLBudgetDtl.FiscalPeriod 
                and GLBudgetDtl.FiscalPeriod <= 30
              ) then 7 else (
                case when (
                  31 <= GLBudgetDtl.FiscalPeriod 
                  and GLBudgetDtl.FiscalPeriod <= 34
                ) then 8 else (
                  case when (
                    35 <= GLBudgetDtl.FiscalPeriod 
                    and GLBudgetDtl.FiscalPeriod <= 39
                  ) then 9 else (
                    case when (
                      40 <= GLBudgetDtl.FiscalPeriod 
                      and GLBudgetDtl.FiscalPeriod <= 43
                    ) then 10 else (
                      case when (
                        44 <= GLBudgetDtl.FiscalPeriod 
                        and GLBudgetDtl.FiscalPeriod <= 47
                      ) then 11 else 12 end
                    ) end
                  ) end
                ) end
              ) end
            ) end
          ) end
        ) end
      ) end
    ) end
  ) end
)
2

Why do you need to nested statement? just use

(case 
    when  (1 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 4) then 1   
    when (5 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 8)  then 2
end)

etc

2 Likes
3

That is exactly what I needed to see! Did not realize I could write it like that. Thanks so much!!

case  
    when (1 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 4) then 1
    when (5 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 8) then 2
    when (9 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 13) then 3
    when (14 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 17) then 4
    when (18 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 21) then 5
    when (22 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 26) then 6
    when (27 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 30) then 7
    when (31 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 34) then 8
    when (35 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 39) then 9
    when (40 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 43) then 10
    when (44 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 47) then 11
else 12
end
1 Like
4

I think you wanted >= on the first first half of each ANDed condition…

when (1 >= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 4) then 1
// note the above is incorrect!  See "whoops" below

WHOOPS!!!
The <= is correct, because you have the constant on the other side.
(I always put the variable first)

But you don’t need the first half of each ANDed condition at all

when (1 <= GLBudgetDtl.FiscalPeriod and GLBudgetDtl.FiscalPeriod <= 4) then 1

is the same as

when (GLBudgetDtl.FiscalPeriod <= 4) then 1
5

Hi Calvin, good point on the AND. That would work on the first condition, but wouldn’t it fail if I did that on every condition in the statement? If it was period 1, I would want to return month 1, but this would not work, because both conditions are met:

when (GLBudgetDtl.FiscalPeriod <= 4) then 1
when (GLBudgetDtl.FiscalPeriod <= 8) then 2
6

once it satisfies one, it’s done. It doesn’t keep going through the loop. So make sure you pay attention to the order when you make something like this.

3 Likes
7

Ah, got it. Thank you for the clarification!

8

3 posts were split to a new topic: Musings on random ways to do things

9

@ckrusen is that really relevant?

2 Likes
10

I think you wanted >= on the first first half of each ANDed condition…

Calvin - I usually put the variable first too :slight_smile: I was seeing if it would accept 1 < x < 4, but it did not like that, and inserting an AND statement was the quickest edit haha