Dolibarr ERP & CRM » Bugs » bug #1336 postgresql cast compta/bank/class/account.class.php:615Latest modifications
Answer now
Snapshot| Details |
| Submitted on: | 2014-04-06 00:55 | | Last Modified On: | 2014-04-06 21:30 |
| Submitted by: | Yann Verry (yverry) | |
| Summary: | postgresql cast compta/bank/class/account.class.php:615 |
| Description: | Bonjour,
Récemment migré en pg avec dolibarr je ne peut pas afficher l'onglet de banques/caisses en raison d'un erreur SQL:
=> SELECT ba.rowid, ba.ref, ba.label, ba.bank, ba.number, ba.courant, ba.clos, ba.rappro, ba.url, ba.code_banque, ba.code_guichet, ba.cle_rib, ba.bic, ba.iban_prefix as iban, ba.domiciliation, ba.proprio, ba.owner_address, ba.state_id, ba.fk_pays as country_id, ba.account_number, ba.currency_code, ba.min_allowed, ba.min_desired, ba.comment, p.code as country_code, p.libelle as country, d.code_departement as state_code, d.nom as state FROM llx_bank_account as ba LEFT JOIN llx_c_pays as p ON ba.fk_pays = p.rowid LEFT JOIN llx_c_departements as d ON ba.state_id = d.rowid WHERE entity IN (1) AND ba.rowid = 1;
ERROR: operator does not exist: character varying = integer
LINE 1: ...LEFT JOIN llx_c_departements as d ON ba.state_id = d.rowid ...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
La solution est d'ajouté un CAST(ba.state_id as numeric) dans la requête. Évidemment cela n'est pas compatible avec MySQL
Cdlt, |
| Step to reproduce bug: | Afficher l'onglet banques/caisses avec une installation PG |
| Detected in version: | 3.5.1 | | Category: | Module: Banks and Cash |
| Severity: | 2 | | OS Type/Version: | Debian |
| PHP version: | 5.5.10 | | Database type and version: | PG9.3 |
| Status |
| Status: | Need more info | | Assigned to: | None |
| Resolution: | None | |
Comments- Yann Verry 2014-04-06 22:59
- On my two databases (MySQL and PostgreSQL) the row in field state_id is null. With your comment I have modified my bank information. Now I have an integer in state_id but is not enough.
Now I have an integer but the type is varchar(50).OK let's go to fix that :
ALTER TABLE llx_bank_account ALTER COLUMN state_id TYPE integer USING (state_id::integer);
After that it's ok. - Laurent Destailleur 2014-04-06 21:30
- With version 3.5, the field state_id of llx_bank_account should be an integer, so cast should be useless.
This means yur database does not match 3.5 format.
Do you have idea why this fields is not numeric ?
|
|
Now I have an integer but the type is varchar(50).OK let's go to fix that :
ALTER TABLE llx_bank_account ALTER COLUMN state_id TYPE integer USING (state_id::integer);
After that it's ok.