Why is my Case with Select Sum Invalid?

[RGALLEN]

CASE 
When  [@field:SRMT_Player_AID ]>=1 THEN Select Sum(target.[@field:Awarded_Points]) FROM SRMT_Player_Movement WHERE SRMT_Player_AID = target.[@field:fk_Player_AID]>=100 THEN "100 Club"
ELSE
'BEGINNER'
END

[CoopperBackpack]

Hello @RGALLEN,

1) First of all, enclose the SELECT statement in parentheses;

2) At the moment you have 2 THEN results one after another:
THEN Select Sum(target.[@field:Awarded_Points]) FROM SRMT_Player_Movement WHERE SRMT_Player_AID = target.[@field:fk_Player_AID]>=100

THEN "100 Club"

The correct syntax is:

CASE
    WHEN condition1   THEN result1
    WHEN condition2   THEN result2
    WHEN conditionN   THEN resultN
    ELSE result
END

3) It is better to use single quotes for the text. For example: '100 Club'.

 

As far as I understood, the condition is: if the SRMT_Player_AID in the current record is greater or equal to 1 AND the sum of the Awarded_Points for the fk_Player_AID of the current record stored in the SRMT_Player_Movement table is greater or equal to 100 then it is '100 club'.

You may test the following formula:

CASE 
WHEN [@field:SRMT_Player_AID] >=1
AND (SELECT SUM(Awarded_Points) FROM SRMT_Player_Movement WHERE SRMT_Player_AID = target.[@field:fk_Player_AID]) >=100
THEN '100 Club'
ELSE 'BEGINNER'
END

If this doesn`t work, please clarify the condition you need to apply.

[PotatoMato]

Just to add, you can also check this article about Case statement: https://howto.caspio.com/function-reference/#:~:text=Return-,Case,-Evaluates a list

 

-Potato