It's failing because you're quoting entire CASE statement. Doing so makes
the SQL interpreter assume your CASE statement is actual a column name on
the "parking" table. If you remove the call to $db->quote() altogether,
though, that won't be enough to fix it. You need to remove that call and
then do one of two things:
1) Surround the contents of the string containing your CASE statement with
parenthesis:
$select = $this->select()->setIntegrityCheck(false);
$select->from('car',array(
'car_id' => 'car_id',
'car_name' => 'car_name',
'quantity' => 'count(car_id)',
)
)
->join('parking','parking.parking_id = car.parking_id',
array(
'license_type' => $db->quote("CASE is_eval WHEN 1
THEN 'EVAL' ELSE 'FULL' END"),
'date_expires' => 'date_expires',
)
);
2) Or, instantiate the CASE statement in a Zend_Db_Expr:
$select = $this->select()->setIntegrityCheck(false);
$select->from('car',array(
'car_id' => 'car_id',
'car_name' => 'car_name',
'quantity' => 'count(car_id)',
)
)
->join('parking','parking.parking_id = car.parking_id',
array(
'license_type' => $db->quote("CASE is_eval WHEN 1
THEN 'EVAL' ELSE 'FULL' END"),
'date_expires' => 'date_expires',
)
);
Ultimately, both are doing the same thing. When ZF sees the "()" in a
column name, it automatically makes it a Zend_Db_Expr. Zend_Db_Expr
basically tells the framework to stay away - perform no escaping - and treat
your string raw SQL code, which is what you need in this case.
On Fri, Feb 6, 2009 at 1:19 PM, rsanaie <[email protected]> wrote:
> Hi all,
>
> I'm trying to have a case statement in my SQL select , but ZF is not
> allowing me, any suggestions:
>
> Here's my code:
>
> $select = $this->select()->setIntegrityCheck(false);
> $select->from('car',array(
> 'car_id' => 'car_id',
> 'car_name' => 'car_name',
> 'quantity' => 'count(car_id)',
> )
> )
> ->join('parking','parking.parking_id = car.parking_id',
> array(
> 'license_type' => $db->quote("CASE is_eval WHEN 1 THEN
> 'EVAL' ELSE 'FULL' END"),
> 'date_expires' => 'date_expires',
> )
> );
>
> and here's the error:
>
> SQLSTATE[42S22]: Column not found: 1054 Unknown column 'parking.'CASE is_eval
> WHEN 1 THEN \'EVAL\' ELSE \'FULL\' END'' in 'field list''
>
> Thanks a bunch for your responses in advance.
>
> ------------------------------
> View this message in context: SQL Case statement in Zend
> Framework<http://www.nabble.com/SQL-Case-statement-in-Zend-Framework-tp21879020p21879020.html>
> Sent from the Zend Framework mailing list
> archive<http://www.nabble.com/Zend-Framework-f15440.html>at Nabble.com.
>
