Fix committed to Yii branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=8580

2.0RC3 released

Can you please have another look at this. You seems have implemented DB type specific SQL and completely breaking MSSQL (and maybe Postgres).

Zut,

I have a look at http://weblogs.asp.net/wilczynski/archive/2008/06/03/sum-case-in-sql.aspx before put this patch.

Use the old method then.

(and tested with pgsql)

Carsten,
Can you give me the MSSQL error ?
It work on pgSQL and mySQL (it's SQL langage, not mysql specific)

And if i look to MSSQL documentation, SUM(CASE() have to work.

Denis

(and after assign it to me)

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]The data types text and varchar are in the equal to-Operator incompatible.. The SQL statement executed was: SELECT COUNT(*) as tkcount,
SUM(CASE WHEN (token IS NULL OR token='') THEN 1 ELSE 0 END) as tkinvalid,
SUM(CASE WHEN (sent!='N' and sent<>'') THEN 1 ELSE 0 END) as tksent,
SUM(CASE WHEN (emailstatus = 'OptOut') THEN 1 ELSE 0 END) as tkoptout,
SUM(CASE WHEN (completed!='N' and completed<>'') THEN 1 ELSE 0 END) as tkcompleted

FROM [lime_tokens_167949]

OK,

Can you test with:

public function summary()
{
    $sid = self::$sid;
    if(Yii::app()->db->schema->getTable("{{tokens_$sid}}")){
        $data=Yii::app()->db->createCommand()
            ->select("COUNT(*) as tkcount, 
                        SUM(CASE WHEN (token IS NULL OR token='') THEN 1 ELSE 0 END) as tkinvalid, 
                        SUM(CASE WHEN (sent!='N' and sent<>'') THEN 1 ELSE 0 END) as tksent, 
                        SUM(CASE WHEN (emailstatus LIKE 'OptOut%') THEN 1 ELSE 0 END) as tkoptout,
                        SUM(CASE WHEN (completed!='N' and completed<>'') THEN 1 ELSE 0 END) as tkcompleted
                        ")
            ->from("{{tokens_$sid}}")
            ->queryRow();
    }
    else
    {
        $data=false;
    }

    return $data;
}

Replace
SUM(CASE WHEN (emailstatus = 'OptOut') THEN 1 ELSE 0 END) as tkoptout,

by

SUM(CASE WHEN (emailstatus like 'OptOut%') THEN 1 ELSE 0 END) as tkoptout,

Tested with mysql and pgsql
And the user can add some information on the OptOut reason for example (i they want)

Thanks

Yes, works fine,

even

SUM(CASE WHEN (emailstatus like 'OptOut') THEN 1 ELSE 0 END) as tkoptout,

works. No % needed.

Fix committed to Yii branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&amp;id=8856

2.00RC6 released.