Alias column in new Drupal7 database API

Question

I'm working through my queries, changing them over to the new API.

I'm trying to SELECT an alias that includes as CASE statement. Here is my original SQL:

SUM(CASE MONTH(data.start) WHEN 1 THEN data.accepted END) AS 'Jan',

And this is what I'm trying with the new API:

->addField('data',  SUM(CASE MONTH('start') WHEN 1 THEN data.accepted END), 'Jan');

However even before I run the query Eclipse is suggesting there is a syntax error here.

Any suggestions would be appreciated.

Many thanks.

Answer 1

You need to use SelectQuery::addExpression()

$expr = "SUM(CASE MONTH('start') WHEN 1 THEN data.accepted END)";
$query->addExpression($expr, 'Jan');