Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
FAW: to replicate a filter from Oracle BI Answer that use ‘contains any’ operator into OAC

Hi,
we have this version FAW 23.R4.
Customer has old system using /analytics prompts and analysis.
When the prompt is passed it is filtering the data that contains clob or bclob data .
In classic home this is based on contain any fitler with presentation variables.
Customer migrated to faw and using oac dv and using expression filters or parameters and query is mainly on how to convert this requirement.
That same reports cannot be used in faw oac classic home as a workaround and as per business requirements we are converting all the classic reports to oac dv .
The field I want to filter has these values inside : '9047_IP|9047_bis|9047_def|9047'
In the database the condition is translated like this :
(("Commesse a piano"."X_COMMESSE_FIGLIE" LIKE '%9047_bis%' OR
"Commesse a piano"."X_COMMESSE_FIGLIE" LIKE '%9047_IP%' OR
"Commesse a piano"."X_COMMESSE_FIGLIE" LIKE '%9047_def%')))
We have opened a SR and support suggests opening an idea request in idea lab .
We ask that this functionality be created in OAC as well.
Thanks Anto
Comments
-
Hi Anto,
You can use the "create expression filter" feature in OAC Data Visualization to do a 'contains any' filter in the workbook / canvas filters section.
0 -
Hi OAwiz-Oracle,
we tried to use the expression filter but there is no way to replicate the functionality ‘contains any’ operator.
We tried to write the expression like this
But the server constructs the condition like this and it's not good, it's syntactically incorrect:
CAP_DW_PROJECT_PIANO_D"."Commesse figlie" like ('9047_capital','9047_def')))
We tried to write the expression like this :
but the validation fails.
We need to focus on the 'contains any' functionality.
Where the "COMMESSE_FIGLIE" field is a CLOB type field that contains many values: 9047_IP|po47_bis|9047_def|9047
In the database the condition is translated like this :
(("Commesse a piano"."COMMESSE_FIGLIE" LIKE '%9047_capital%' OR
"Commesse a piano"."COMMESSE_FIGLIE" LIKE '%9047_def%' OR
"Commesse a piano"."COMMESSE_FIGLIE" LIKE '%po47_bis%')))
We have to do the same table.field LIKE '%comessa operativa param%'
IF pv_comessa_operativa has more values then it add the OR
I hope I have explained myself better now.
Do you have any suggestions for constructing the expression correctly?
Thanks Anto
0 -
Try something like this:
Insert your presentation variable name here --> <PresVar>
If above method does not work, what I would also suggest is to grab the logical SQL from the OAC classic analysis and then use that to create your workbook in DV using Developer Options
0 -
Hi OAWiz-Oracle,
We have already tried but it doesn't work if the variable has multiple values in the filter:
Stato: HY000. Codice: 43275. [nQSError: 43275] Messaggio restituito da OBIS [ecid:40c31139-f8ff-48bd-9d2e-de6d5cb4a80b-000613cd,0:1:2:4 ts:2024-01-18T12:26:18.535+00:00].(HY000)Stato: HY000. Codice: 27002. [nQSError: 27002] Accanto a <,>: errore di sintassi (HY000)
annuali e totali commesse piano')."CAP_DW_PROJECT_PIANO_D"."Commesse figlie" like CONCAT(CONCAT('%', ('9047','9047_def')), '%')OR XSA('m.zonelli@reply.it'.'CAP - Costi e ricavi annuali e totali commesse piano')."CAP_DW_PROJECT_PIANO_D"."Commesse figlie" like '%PIPPO%'))
For "If above method does not work, what I would also suggest is to grab the logical SQL from the OAC classic analysis and then use that to create your workbook in DV using Developer Options"
Can you give an example?
Thanks Anto
0 -
I just tried to replicate that myself to show you an example but it doesn't work for me anymore. In this case, you may have to stick with Classic for this report and/or use case.
Unless anyone else has any ideas?
0