"cdbcommand Failed To Execute The Sql Statement: Sqlstate[42S22]: Column Not Found: 1054 Unknown Column 'amt' In 'where Clause'."

Yii 1.1 General Discussion
1 



class Transactions extends CActiveRecord{


public $Amt;


public function GetTodayPaymentList($ShowType)

    {

        $criteria = new CDbCriteria;

        $criteria->select = 'sum(Amount) as Amt';

        $criteria->with = array('user.userInfos');

        $criteria->together = true;

        $criteria->condition = 'Amt>=userInfos.WithdrawalValue';

        $criteria->group = 't.UserID';

        return new CActiveDataProvider($this, array('criteria'=>$criteria));

    }

}

This is my code for listing all the user which having sum() transaction Amount more than the userinfo WithdrawalValue.

But here am getting the error like

“CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Amt’ in ‘where clause’.”

Please advise me how can i solve this error

2

Hi,

Please can you paste your raw query from your log files.

Thanks

chandran nepolean

3

I don’t think this belongs into the condition part at all. Do you require the sum somewhere? If not, try




$condition->having='SUM(`Amount`)>=userInfos.WithdrawalValue';
4

Filtering by an aggregate function is done in the HAVING clause, because it’s executed after computing the aggregate value. Set that condition in the ‘having’ property of criteria object.

5

yes i need sum and i am getting the value…

Realy thanks, working fine

6

Hi,

I just replaced $criteria->condition to $criteria->having then while am trying to generate the cgridview details there am getting the total count is different, means below please find the two related queries which yii executing while calling the cgrdiview

For getting the count




Querying SQL: SELECT COUNT(DISTINCT `t`.`ID`) FROM `tbltransactions` `t` 

LEFT OUTER JOIN `tblusers` `user` ON (`t`.`UserID`=`user`.`ID`)  LEFT OUTER

JOIN `tbluser_info` `userInfos` ON (`userInfos`.`UserID`=`user`.`ID`)

For getting the data this is the query




SQL: SELECT sum(Amount) as Amt  FROM `tbltransactions` `t`  LEFT

OUTER JOIN `tblusers` `user` ON (`t`.`UserID`=`user`.`ID`)  LEFT OUTER JOIN

`tbluser_info` `userInfos` ON (`userInfos`.`UserID`=`user`.`ID`) GROUP BY

t.UserID HAVING (Amt>=userInfos.WithdrawalValue) LIMIT 10

both query is different…how it is happening in my code i am writing the the second query… for getting the count yii generating the query… why yii generating different using different crietera for getting the count and data.Please advise…

7

The COUNT() would get odd values due to the grouping if you’d do it in one query.

8

my Yii version on local was 1.1.14 and on server 1.1.12,

in local was working fine and when i update server framework also to 1.1.14 now gridview also working,

so problem it was for Yii Version…