can we use field name for comparison in case statement?

[shivi_tcs]

Hi Splunkers!

I am try to evaluate few things by using query below-

index=* sourcetype=* | stats values(OPEN_INT) as int by
OPTION_TYP STRIKE_PR | appendcols [|search index=* sourcetype=*
OPTION_TYP=XX | eval a1=CLOSE-(CLOSE*75)/10000|eval a2=CLOSE+(CLOSE*75)/10000|
eval i2=CLOSE-(CLOSE*25)/1000 | eval o2=CLOSE+(CLOSE*25)/1000 |table a1 a2 i2 o2 CLOSE]|
eval ty=case(STRIKE_PR>=9839.46 AND STRIKE_PR<10016,"IN",STRIKE_PR>=10016 AND STRIKE_PR<10167,"AT"
,STRIKE_PR>=10167 AND STRIKE_PR<=10344,"OUT",1==1, NULL) | search ty!=NULL |

I need to use the values of fields a1 a2 i2 o2 in the case statement written above, such that my statement appears like-
eval ty=case(STRIKE_PR>=i2 AND STRIKE_PR=a1 AND STRIKE_PR=a2AND STRIKE_PR<=o2,"OUT",1==1, NULL) | search ty!=NULL |

But splunk doesn't give me any results when i use fields name instead of the numeric value.

Can someone figure out what the problem is?

[rjthibod · Accepted solution]

Yes, you can use fields in case statements. Here is a simple example that proves it.

| makeresults 
| fields - _time
| eval thresh = 3, value = 3
| eval result = case(thresh > value, "lower", thresh < value, "higher", thresh == value, "equal", 1==1, "0")

Without having your actual data, one suggestion I would make is replace NULL with NULL() in the case statement. Then change the following search to | WHERE isnotnull(ty). Maybe the fact that you are using search against a table of field values and not _raw is the issue.

So the modified search would be

index= sourcetype= 
| stats values(OPEN_INT) as int by 
OPTION_TYP STRIKE_PR 
| appendcols [|search index= sourcetype= 
OPTION_TYP=XX | eval a1=CLOSE-(CLOSE*75)/10000|eval a2=CLOSE+(CLOSE*75)/10000| 
eval i2=CLOSE-(CLOSE*25)/1000 | eval o2=CLOSE+(CLOSE*25)/1000 |table a1 a2 i2 o2 CLOSE]
| eval ty=case(STRIKE_PR>=9839.46 AND STRIKE_PR<10016,"IN",STRIKE_PR>=10016 AND STRIKE_PR<10167,"AT"
,STRIKE_PR>=10167 AND STRIKE_PR<=10344,"OUT",1==1, NULL()) 
| where isnotnull(ty)

[shivi_tcs]

Hi,
Thanks for replying.
I got a part of solution from your answer!

[rjthibod]

Glad to hear you got it cleared up. Please award points or accept it as the answer if your issue is resolved.