We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Error with PHQL Cast to Decimal

When attempting to execute the following query:

$vehicleParts = $this->modelsManager->executeQuery("
SELECT WP\Models\Transactions.Date, WP\Models\Parts.Description, CAST(WP\Models\Transactions.Value AS DECIMAL(5,2)) * -1 as Cost, 
WP\Models\Agents.FirstName as PurchasedFrom FROM WP\Models\InventoryVehicles
INNER JOIN WP\Models\TransactionInventoryVehicleParts on 
WP\Models\InventoryVehicles.Id = WP\Models\TransactionInventoryVehicleParts.Inventory_Vehicles_Id
INNER JOIN WP\Models\Parts on  
WP\Models\Parts.Id = WP\Models\TransactionInventoryVehicleParts.Parts_Id
INNER JOIN WP\Models\Transactions on   
WP\Models\Transactions.Id = WP\Models\TransactionInventoryVehicleParts.Transactions_Id
INNER JOIN WP\Models\Agents on
WP\Models\Agents.Id = WP\Models\Parts.Supplier_Agents_Id
WHERE WP\Models\InventoryVehicles.Id = :vehicleId:", array(
'vehicleId' => $inventoryVehicle->Id));

I get the following error:

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token (, near to ''5','2')) * -1 as Cost, WP\Models\Agents.FirstName as PurchasedFrom FROM WP\Models\InventoryVehicles INNER JOIN WP\Models\TransactionInventoryVehicleParts on WP\Models\InventoryVehicles.Id = WP\Models\TransactionInventoryVehicleParts.Inventory_Vehicles_Id INNER JOIN WP\Models\Parts on WP\Models\Parts.Id = WP\Models\TransactionInventoryVehicleParts.Parts_Id INNER JOIN WP\Models\Transactions on WP\Models\Transactions.Id = WP\Models\TransactionInventoryVehicleParts.Transactions_Id INNER JOIN WP\Models\Agents on WP\Models\Agents.Id = WP\Models\Parts.Supplier_Agents_Id WHERE WP\Models\InventoryVehicles.Id = :vehicleId:', when parsing: SELECT WP\Models\Transactions.Date, WP\Models\Parts.Description, CAST(WP\Models\Transactions.Value AS DECIMAL('5','2')) * -1 as Cost, WP\Models\Agents.FirstName as PurchasedFrom FROM WP\Models\InventoryVehicles INNER JOIN WP\Models\TransactionInventoryVehicleParts on WP\Models\InventoryVehicles.Id = WP\Models\TransactionInventoryVehicleParts.Inventory_Vehicles_Id INNER JOIN WP\Models\Parts on WP\Models\Parts.Id = WP\Models\TransactionInventoryVehicleParts.Parts_Id INNER JOIN WP\Models\Transactions on WP\Models\Transactions.Id = WP\Models\TransactionInventoryVehicleParts.Transactions_Id INNER JOIN WP\Models\Agents on WP\Models\Agents.Id = WP\Models\Parts.Supplier_Agents_Id WHERE WP\Models\InventoryVehicles.Id = :vehicleId: (902) File=C:\xampp\htdocs\whitepigeon\app\controllers\InventoryVehiclesController.php Line=327 #0 [internal function]: Phalcon\Mvc\Model\Query->parse() #1 [internal function]: Phalcon\Mvc\Model\Query->execute(Array, NULL) #2 C:\xampp\htdocs\whitepigeon\app\controllers\InventoryVehiclesController.php(327): Phalcon\Mvc\Model\Manager->executeQuery('\r\n S...', Array) #3 [internal function]: WP\Controllers\InventoryVehiclesController->viewAction('1') #4 [internal function]: Phalcon\Dispatcher->dispatch() #5 C:\xampp\htdocs\whitepigeon\public\index.php(29): Phalcon\Mvc\Application->handle() #6 {main}

When I remove the M,0 part of the decimal cast it works fine though.

Any thoughts



24.1k

perhaps try to bind the value ?

DECIMAL(:M:, :D:)



2.5k

I tried your suggestion and still ran into an error

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token (, near to ':M:, :D:) as Cost


10.9k

Maybe CAST(WP\Models\Transactions.Value AS DECIMAL(5,2)) as Cost without the * -1 is worth a go just to make sure.



10.9k
edited Sep '14

Even though it's complaining about the ( after DECIMAL. Also what version MySQL is it?



2.5k

Im using MySQL v5.6.16 Community Server,

I tried to remove the * -1 and luck on resolving the issue.



10.9k
Accepted
answer

I have a feeling that this is something that you will have to find a workaround for, I'm not spotting anything wrong with it. You could maybe try some raw SQL. Example:

    $sql = "SELECT Transactions.Date, 
                Parts.Description, 
                CAST(Transactions.Value AS DECIMAL(5,2)) * -1 as Cost, 
                 ... etc etc"

    $model = new \MyModel();
    $result  = $model->getReadConnection()->query($sql);


2.5k

Thanks, What i ended up doing was just did the formatting for the currency in the view rather than trying to get the data somewhat formatted for the view.

I appreciate the help.