We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

PHQL Syntax error, unexpected token IDENTIFIER(when)

I was trying make this query by PHQL:

$query = $this->modelsManager->createQuery("select team, sum(case when used = 0 and team = 2 then points end) totalpoints from Genkeys join Tasks on Genkeys.uloha = Tasks.id group by team");
$Genkeys = $query->execute();
    foreach ($Genkeys as $genkey) {
        $this->view->disable();
        var_dump($genkey);
    }

Whe I tryed that in phpmyadmin as raw sql it was working fine but when I run it in controller I see this error:

Syntax error, unexpected token IDENTIFIER(when), near to ' used = 0 and team = 2 then points end) totalpoints from Genkeys join Tasks on Genkeys.uloha = Tasks.id group by team', when parsing: select team, sum(case when used = 0 and team = 2 then points end) totalpoints from Genkeys join Tasks on Genkeys.uloha = Tasks.id group by team (143)


33.8k

Try (used = 0 and team = 2) or set an ELSE in the statement.



5.2k
in reply to RompePC
edited Oct '14

Try (used = 0 and team = 2) or set an ELSE in the statement.

But then it will not sum points I need to sum points but only of that rows where used is 0 and only of specific team.



98.9k
Accepted
answer
edited Oct '14

This syntax: case when used = 0 and team = 2 then points end is not supported by PHQL, you can use an IF:

IF(used = 0 and team = 2, points, 0)



5.2k

thanks



2.5k

It works for Mysql, but not with Postgresql.

This problem is nicely solved in Kohana framework and it's very useful.

https://kohanaframework.org/3.3/guide/database/query/builder#database-expressions

Something like this would be great in Phalcon. What do you think?



555

Why is this marked as Solved when it still doesn't work in PostgreSQL?



34.6k

Starting from 2.0.3 you can use CASE: https://blog.phalcon.io/post/phalcon-2-0-3-released



555
edited May '16

I'am using 2.0.8 and this valid Postgresql statement is still not working:

CASE WHEN file_size IS NOT NULL THEN file_size[0] || 'x' || file_size[1] ELSE NULL END AS file_size

leads to the following Error:

Syntax error, unexpected token WHEN, near to ' file_size



34.6k

The problem is not the CASE, it's that you're trying to concatenate using || which is a postgresql-only operator, you can:



555
in reply to Andres Gutierrez
edited May '16

Same error on:

CASE WHEN file_size IS NOT NULL THEN file_size ELSE NULL END AS file_size

:(

The problem is not the CASE, it's that you're trying to concatenate using || which is a postgresql-only operator, you can:



80
in reply to Phalcon
edited Apr '19

It count 1 count great, but have countshow2 then result error please how to more result count

$query->columns( [ "idshow"=>"s.created_by", "countshow"=>"count(IF(s.state = 'unfit', 1, 0))", "countshow2"=>"count(IF(s.state = 'fit', 1, 0))" ] );

This syntax: case when used = 0 and team = 2 then points end is not supported by PHQL, you can use an IF:

IF(used = 0 and team = 2, points, 0)