0029919: The query done to populate table/tabledir dropdowns in forms when the user enters text can be improved

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0029919

Openbravo ERP

A. Platform

public

2015-05-18 18:35

2022-02-01 08:09

Reporter

AugustoMauch

Assigned To

Triage Platform Base

Priority

urgent

Severity

major

Reproducibility

have not tried

Status

acknowledged

Resolution

open

Platform

OS Version

Product Version

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0029919: The query done to populate table/tabledir dropdowns in forms when the user enters text can be improved

Description

If the user enters text in the dropdown of a table/tabledir field, the query created to populate the combo will use the coalesce and to_char functions even if it is not needed.

For instance, if the user enter 'u' in the field Organization of the header tab of the Sales Order window, the following query will be used to populate the dropdown:

SELECT td0.AD_Org_ID AS ID, (COALESCE(TO_CHAR(COALESCE(TO_CHAR(td0.Name),'')),'')) AS NAME, '' AS DESCRIPTION
FROM AD_Org td0
WHERE td0.AD_Client_ID IN ('0','23C59575B9CF467C9620760EB255B389')
AND td0.AD_Org_ID IN ('2E60544D37534C0B89E765FE29BC0B43','7BABA5FF80494CAFA54DEBD22EC46F01','B843C30461EA4501935CB1D125C9C25A','BAE22373FEBE4CCCA24517E23F0C8A48','DC206C91AA6A4897B44DA897936E0EC3','E443A31992CB4635AFCAEABE7183CE85')
AND (AD_ORG_ID <> '0')
AND (td0.isActive = 'Y' OR td0.AD_Org_ID = (?) )
AND UPPER((COALESCE(TO_CHAR(COALESCE(TO_CHAR(td0.Name),'')),''))) like UPPER(?)
ORDER BY 2 LIMIT 76 OFFSET 0

The name of the organization (td0.Name) is a mandatory textual column, so there is no need to surround it with coalesce and to_char functions.

Steps To Reproduce

As F&B Admin:
- Open the Sales Order window
- Create a new record in the header tab
- Enter the character 'u' in the Organization field. The query generated will be the one pasted in the description. You can check it yourself by looking at what this line returns in the ComboTableData.select function:

String strSql = getQuery(false, null, null, startRow, endRow, conn,
!StringUtils.isEmpty(filterValue));

Proposed Solution

Additional Information