How to work with nested Ifs and cases in a query?

[mjon395]

Hello,

It's possible that I've had too long of a day, but I can't wrap my head around nesting many ifs.  Is anyone willing to help me out?  I am really bad at writing out SPL queries to make it visually understanding with parentheses and commas.  Does anyone have some additional tips on that as well that would be useful for these nested scenarios?

For example:

| eval new_field = if(pass_fail="fail",
  if(importance="0" OR importance="1",
    case(
        Days<7 OR State="Online","Gold",
	Days >=7 AND Days<14,"Orange",
	Days>=14,"Red"),
    if(importance="2",
        case(
	    Days<30 OR State="Online","Gold",
	    Days >=30 AND Days<60,"Orange",
	    Days>=60,"Red"),
        if(importance="3",
            case(
	      Days<60 OR State="Online","Gold",
	      Days >=60 AND Days<120,"Orange",
	      Days>=120,"Red"),
            "importance_3_false"),
        "importance_2_false"),
    "importance_1_0_false"),
"main_if_fail")

The idea is to break out into a newfield by first looking at only the "fail" items, and then further breaking down the "fail" items by their importance (which can be 0, 1, 2, 3) where 0&1, 2, and 3 have their own case statements.  All the case statements and ifs should be true, and the "importance_3_false" (for example) are more for debugging and should never actually show in my output.

I appreciate any help and thank you.

Error in 'eval' command: The arguments to the 'if' function are invalid.

[bowesmana · Accepted solution]

I believe you may be looking for this syntax

| eval new_field = if(pass_fail="fail",
  case(
    importance="0" OR importance="1", case(
        Days<7 OR State="Online","Gold",
	    Days >=7 AND Days<14,"Orange",
	    Days>=14,"Red",
	    1==1,"importance_1_0_false"),
    importance="2", case(
	    Days<30 OR State="Online","Gold",
	    Days >=30 AND Days<60,"Orange",
	    Days>=60,"Red",
	    1==1,"importance_2_false"),
    importance="3", case(
        Days<60 OR State="Online","Gold",
	    Days >=60 AND Days<120,"Orange",
	    Days>=120,"Red",
        1==1,"importance_3_false"),
     1==1,"importance_fail"),
     "main_if_fail")

It can often be confusing to use nested ifs, so above has a single if checking for pass_fail="fail".

After that it checks importance value with CASE not IF and then has the nested case to check for the Days condition.

Then there is a1==1,"importance_fail", which is the case where pass_fail="fail" and importance does not match 0,1,2 or 3.

Hope this helps.

[mjon395]

Thanks!  This worked perfectly, and the nested cases instead of ifs really makes it a lot more understandable for me.  I'm pretty sure I will need to go one step deeper, so a nested nested case, but should be doable with some trial and error!

Thanks for pointing me the right way.