Creating Nested calculated field

Corey Humphrey shared this question 2 years ago
Answered

IS their a way to create a Nested Case statement in the calculated field box?

every time a try I get the message, "Close bracket not found"

Comments (2)

photo
1

Hi Corey,

This should be possible within a freehand SQL statement, can you send me a screenshot of your calculated field and the error you are receiving?

Regards,

Nathan

photo
1

I am with BMC smart reporting and I belive they have removed the freehand SQL.

but here is a screen shot.

photo
1

HI Corey,

From what I can see, you do not need the initial parenthesis before the original CASE.

Additionally, you are surrounding odd things in parentheses (specifically the highlighted portion in your screen-shot). I would write like:

CASE WHEN ((A=1 OR A=2) AND (B=1 OR B=2)) THEN ___________ END

Are you able to get this to work in queries against the database directly (if you have access to it)

Regards,

Nathan

photo
1

I am new to (case) statements, I used (if elseif) statements before Business Objects software and got it to work. I am trying to buckets tickets in certain categories.

but basically in that statement I want to capture all tickets that are not resolved and are from level 1 and assigned to a specific group to have a particular category, then I want all tickets that are not resolved and are from level 1 and are not assigned to a particular group to be in a category, then all other tickets will be from level 2.

photo
1

Hi Corey,

While I cannot speak to your exact use case, the problem seems to be specifically in your WHEN clauses specifically (aka IF). When you write a case statement simple logic such as a single and/or, you will not need any parenthesis:

CASE WHEN A=1 OR A=2 THEN B END

This would be the same as


IF (a=1 || a=2){
   B
 } 

If you want to nest this logic, then you will need to user parentheses:

CASE WHEN (A=1 OR A=2) AND B=1 THEN C

Let me know if this makes sense.

Regards,

Nathan

photo
photo
1

Hi Corey,

Any luck here? I am going to close this for now, but let me know if there is anything else I can do to help.

Regards,

Nathan

photo
1

ok no problem, no luck as of now,

photo