CASE in grafted field SQL

[John3136]

I've got a new requirement for my ancient Xataface system.

The system is used to enter scores for a few variations on one sporting match.

In the past the maximum score per person was based on the particular match (i.e. there might be 4 members of a team and the maximum score for any member was the same, so it was a property of the matches table).

Now we are changing it so that all 4 positions on the team can have a different maximum.

I've added a new table like: MaxScores: MatchId, Position, MaxScore

So for matchid=1, position=1, max=100. matchid=1, position=2, max=80 etc.

I want to use the MaxScore from MaxScores if there is one else defer back to the original in the Matches table. In SQL I can use a clause in the select like:

CASE WHEN EXISTS (SELECT MaxScore FROM MaxScores ms WHERE ms.MatchId = matches.MatchId and ms.Position = scores.Position) THEN MaxScores.MaxScore ELSE matches.maxScore

I tried to graft this into the SQL for the scores table but I got an error on the case

Fatal error: Failed parsing SQL query on select: <query>  found: "CASE" in C:\wamp64\www\postals\xataface-2.2.5\lib\SQL\Parser.php on line 1829

The SQL works as expected normally but clearly Xataface can't parse it,.

Can anyone suggest how I can pick the right Max?

Plan B which I want to avoid is populating the MaxScores table with every position of every match and making it the one and only source of the MaxScore. That is a lot of redundant data since only 3 out of 20 events use the new way :-(

Hope I've explained it clearly enough to get a suggestion...

[Patrick Reinhard]

I tried using a CASE clause in my Xataface test app and got the same error.

In my opinion, you could bypass the problem:

1) using a IF() instead of a CASE statement as follows:
SELECT
...
, IF(EXISTS(SELECT MaxScore FROM MaxScores ms WHERE ms.MatchId = matches.MatchId and ms.Position = scores.Position), MaxScores.MaxScore, matches.maxScore) AS ChosenMaxScore
...
FROM
matches mt
...

2) using COALESCE and a LEFT JOIN:

SELECT
...
, COALESCE(ms.MaxScore, mt.maxScore) AS ChosenMaxScore
...
FROM
...matches mt
LEFT JOIN MaxScores ms ON ms.MatchId = mt.MatchId and ms.Position = scores.Position
...

3) putting your SQL query in a view and using that view in fields.ini

HTH

[Steve Hannah]

Yeah. The Xataface SQL query parser doesn't support CASE statements yet.  But, as Patrick points out, it does support IF statements, and some other alternatives.

--
You received this message because you are subscribed to the Google Groups "Xataface" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xataface+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/xataface/92367f2a-b44e-4b59-8b88-f03b92ac69edn%40googlegroups.com.

--

Steve Hannah

Web Lite Solutions Corp.

[John3136]

Thanks guys.

Using option 1 "IF EXISTS(..." seems to be working as expected :-)