COUNT field incorrect or syntax error

Yii 2.0 General Discussions
1

I’m having an issue with the following query that works fine in MySQL, but err in SQL Server.

SELECT COALESCE(t1.UnitPrice, t2.UnitPrice) AS Price
FROM lst_pilot_car_items AS t2
LEFT JOIN
  (SELECT *
   FROM clients_price_list
   WHERE ClientId=2
     AND PilotCarItemId='797' ) AS t1 ON t2.PilotCarItemId=t1.PilotCarItemId
WHERE t2.PilotCarItemId='797'

The error reported is

Exception (Database Exception) 'yii\db\Exception' with message 'SQLSTATE[07002]: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error

The actual code behind the query is

$clientId = ProjectsLegs::findOne($leg)->proj->client->ClientId;
$SQL = "SELECT COALESCE(t1.UnitPrice, t2.UnitPrice) AS Price 
        FROM lst_pilot_car_items As t2 
        LEFT JOIN (
            SELECT UnitPrice 
            FROM clients_price_list 
            WHERE ClientId = :ClientId AND PilotCarItemId = :PilotCarItemId
        ) As t1 ON t2.PilotCarItemId = t1.PilotCarItemId
        WHERE t2.PilotCarItemId = :PilotCarItemId";
$params = [':ClientId' => $clientId, ':PilotCarItemId' => $item];
$connection = Yii::$app->getDb();
$command = $connection->createCommand($SQL, $params);
$result = $command->queryScalar();
if ($result == FALSE) {
    return 0;
}else{
    return $result;
}

If I take the generated SQL and plug it into SSMS, it runs just fine, so I’m thinking some type of bug with Yii?

Is there some way around this?