nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

In criteria tab when applying a filter only 1 out of 2 unique values appears

User_YOR5U

OAS 5.9

Hello Experts,

I have a column named 'TYPE' which has 2 unique values 'DOMESTIC' and 'INTERNATIONAL'. When I try to filter the column 'TYPE' it only shows 'DOMESTIC' and does not show 'INTERNATIONAL'. However in results tab all data comes i.e it shows both 'DOMESTIC' and 'INTERNATIONAL'. Any help in investigating why while applying the filter only 'DOMESTIC' option comes would be really helpful. When I type 'INTERNATIONAL' in filter search bar then it shows up in the search, but without explicitly searching only 'DOMESTIC' comes.

Screenshot 2024-03-19 183952.png

Screenshot 2024-03-19 183535.png

Total values in the type column are 50000 rows out of which 'DOMESTIC' comes 20000 times and 'INTERNATIONAL' comes 30000 times. There is no null value. The Data type in both rpd and database is varchar 20

I tried a workaround: When I do cast(type as char) then both the values appear in filter tab, however I cannot implement this solution. So, I need to find out why one value is not coming.

Find more posts tagged with

Accepted answers

User_YOR5U

Hi @Gianni Ceresa @Christian Berg,

Thanks for your help. After reading the physical query generated in the log we noticed that OAS was not applying distinct. This was because type was a key. Now this issue is resolved. Thanks again for your help

All comments

Gianni Ceresa

First thing: OAS 5.9 is our of support, you should really consider an upgrade (OAS 7.6 aka 2024 was released just yesterday), 5.9 is from 2021 and OAS only has 2 years of support.

If you look at the session logs you will see the query executed to show the values of the filter. Based on that you will be able to identify what solution you could implement (the query is generated based on your RPD, therefore it is impossible to say much about that).

If the only solution is to retrieve more data, there is probably a parameter defining the number of records retrieved for filters.

User_YOR5U

Hi Gianni,

We are upgrading to cloud soon, if I run the physical query in database I get the right result i.e both International and domestic comes. Its only when I apply the filter in criteria I see only the Domestic Option. Any idea where will I find parameter detail for filters.

[Deleted User]

Your first starting point is always the log. What query does the log actually capture? What's the SQL and what's the WHERE clause? That SQL will then lead you to start investigating the next step:

The model. Your models defines everything and all the logic the engine uses to produce the SQL. If you have a TYPE column with 2 value then the SQL should result in 2 "rows" representing the 2 values. Doesn't matter if each value exists once, 12 times, 40000 times or 2 billion times.

Gianni Ceresa

https://community.oracle.com/products/oracleanalytics/discussion/comment/52853#Comment_52853

Do you see the query fired when you click on the column in the filter and go to select the values? Because there as well a query is executed, and the result is used to show you the values. It is visible in the session logs or the log files.

User_YOR5U

Hi @Gianni Ceresa @Christian Berg,

Gianni Ceresa

Good to hear: the answer is (almost) always in the query :D

[Deleted User]

https://community.oracle.com/products/oracleanalytics/discussion/comment/52863#Comment_52863

And when the physical query is no the answer, then it's almost always because the log level isn't high enough ;)