The sort tab is not working when the identifier is a virtual column. Hit into error as the System is trying to look for the physical column.
When there is more than one identifier, the System is trying to use the SQL Coalesce function to handle the NULL values. Hit into 'inconsistent datatypes' error in Postgres when the identifiers have a different data type.
I checked the pull request https://github.com/idempiere/idempiere/pull/173
I would suggest instead of creating a dependency on DB.isPostgreSQL - if we can better change the query to something standard that is valid in oracle and postgres.
Something like COALESCE for every variable should work, I mean, the output of this query is the same in oracle and postgresql:
Hi , that coalesce doesn’t work if one of the identifier is not of text data type.
Perhaps coalesce(cast(… as varchar(100),'') || … will works for both Oracle and PostgreSQL but that’s a pretty awkward sql for PostgreSQL.
Yes, something like that is what the report engine uses to display the identifier of a record.
Debugging there I did a test defining as identifiers of C_BP_Group the columns Value, Name, C_BP_Group and AD_Org_ID; the resulting SQL in report engine for this column is this one:
I checked and that SQL "column" is returned from the MLookupFactory.getDisplayColumn
CAST (C_BP_Group.C_BP_Group_ID AS Text) is invalid for Oracle, there’s also conversion or DB.IsPostgresql involve here as well.
Yes, from what I reviewed the CAST AS TEXT is converted on DB_PostgreSQL.TO_CHAR.
I didn't check how is managed in postgres native mode.