Search Not Working In Data Model List Of Values With Message 'No Data Found' When Using SQL With Concatenated Columns (Doc ID 2951322.1)

Last updated on MAY 25, 2023

Applies to:

Symptoms

On : 11.13.23.04.0 version, Analytics


Search not working in Data Model with List of Values setup using SQL Query that has concatenation operator -

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Login in to Fusion Applications

Choose Tools -> Reporting and Analytics , Browse Catalog
Create ->Data Model or open existing one, then choose View Data
Search the available Sales Reps values
No data found

Detailed Steps:

Create Data Model

List Of Values
  Name: REPS_LOV
  Type: SQL Query
  Data Source:  ApplicationDB_CRM




List of Values SQL

SELECT DISTINCT
participants.HR_PERSON_NUMBER || ', ' || participants.participant_name,
participants.HR_PERSON_NUMBER
FROM
CN_SRP_PARTICIPANTS_ALL participants,
hr_all_organization_units_vl hou
WHERE
1 = 1
and participants.org_id = 300000010376368
and PARTICIPANT_NAME like '%<name>%'
AND hou.organization_id = participants.org_id
AND participants.PARTICIPANT_TYPE = 'PARTICIPANT'


Parameters
  Name: REPS_parmtr
  Data Type: String
  Parameter Type: Menu
  Display Label: Sales Rep
  List of Values: REPS_LOV
  Number of Values to Display in List:  100



Choose button for  View Data

This shows the list of existing values and option to search -

 Sales Rep
        766, Name1
        866, Name2
        Search ...       F12


Choose Search
 Name    Starts With
 Enter    766

No data found

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.