Sort tab does not support virtual column identifier and identifiers with different data type

Description

Issue #1:
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.

Issue #2:
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.

Environment

None

Activity

Show:
Carlos Ruiz
July 20, 2020, 9:58 PM

Hi ,

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:

Heng Sin Low
July 21, 2020, 6:58 AM

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.

Carlos Ruiz
July 21, 2020, 11:13 AM

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

Heng Sin Low
July 21, 2020, 11:59 AM

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.

Carlos Ruiz
July 21, 2020, 3:44 PM

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.

Assignee

Heng Sin Low

Reporter

Elaine Tan

Labels

None

Tested By

None

Fix versions

Priority

Minor
Configure