LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Pavan

Hi I neet to convert case statement in sql into lavastrom. Please tell me how to convert execute this lavastorm.


[B]SELECT paymenttype,
SUM(CASE WHEN 0 <= totalprice AND totalprice < 10
THEN 1 ELSE 0 END) as cnt_0_10,
SUM(CASE WHEN 10 <= totalprice AND totalprice < 100
THEN 1 ELSE 0 END) as cnt_10_100,
SUM(CASE WHEN 100 <= totalprice AND totalprice < 1000
THEN 1 ELSE 0 END) as cnt_100_1000,
SUM(CASE WHEN totalprice >= 1000 THEN 1 ELSE 0 END) as cnt_1000,
COUNT(*) as cnt, SUM(totalprice) as revenue
FROM orders
GROUP BY paymenttype
ORDER BY 1

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

This is much less complicated in an Agg node in LAE.

The case statements could be done with if statements. But for this you can actually put the boolean expression into count() and then later emit that results:

cnt_0_10 = count(0 <= totalprice and totalprice < 10)
cnt_10_100 = count(10 <= totalprice and totalprice < 100)
cnt_100_1000 = count(100 <= totalprice and totalprice < 1000)
...
emit cnt_0_10
emit cnt_10_100
emit cnt_100_1000
...
where lastInGroup

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Pavan

Thanks Jones.....

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

Hi,

I want to implement the case statement in lavastorm. I tried to achieve the same in IF statement, but I'm not getting an error, input variable is not getting resolved in the regex().

Input records:

flag:string, month:string,month1:string,month2:string,month3:st ring
Y,JAN18,FEB18,NULL,NULL
Y,FEB18,MAR18,NULL,NULL
N,JAN18,NULL,NULL,NULL
Y,NULL,APR18,NULL,NULL
Y,NULL,OCT18,NULL,MAY18
Y,NULL,DEC18,NULL,NULL

filter node :

Search_List1 = "JAN18,FEB18,MAR18,APR18,MAY18,JUN18,JUL18"


if (flag == "Y" and Search_List1.regexIsMatchI('month')) then test_output = 'month'
else if (flag == "Y" and Search_List1.regexIsMatchI('month1')) then test_output = 'month1'
else if (flag == "Y" and Search_List1.regexIsMatchI('month2')) then test_output = 'month2'
else if (flag == "Y" and Search_List1.regexIsMatchI('month3')) then test_output = 'month3'
else test_output = "NO "

emit test_output

Expected Output :

test_output
JAN18
FEB18
NO
APR18
MAY18
NO

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

Hi Arunn,

this should give you the results you want.

Regards,
Adrian

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

RegEx doesn't like a null value for the search key.
In this case, since you are not using actual expressions such as * or [a-z], then it STRFIND will be more efficient, and it handles the null the way you wish.

s

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

Thanks Adrian !! It's working as expected.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Arunn

Thanks Smith !! It's really more efficient and it's working as expected.