Oracle Communities

Oracle Analytics and AI

Child Item

How can I use lower function in RPD physical layer?

i have situation that has one of column data has mixed case in one table. so that join would skip that data.

other table column's all lower case.

I want to capture the data if i can lower the data case, but i can't do it on physical layer or I have to do it from database side.

I want to keep original data but for the join i want to use it all lower case.

Any idea?

Find more posts tagged with

Accepted answers

Gianni Ceresa

When you create a physical join, you go directly in the Expression Builder before to click "Ok" and create it. There you can write your condition using functions or anything else you need.

If the join already exists, and it is a "Physical Foreign Key", you can't change it. You need to delete it first and add the join again, go in the Expression Builder and write your expression. If you use a function, the join will not be created as a "Physical Foreign Key" but as a "Complex join".

All comments

Gianni Ceresa

In your semantic model (the RPD), you just define the physical join with the LOWER() function on your column. You can set "complex joins", where you are allowed to write formulas and not just select column1 = column2.

Tony_OAS_Apex_User

Gianni, thanks for the quick answer. where can i set the complex joins?

Gianni Ceresa

When you create a physical join, you go directly in the Expression Builder before to click "Ok" and create it. There you can write your condition using functions or anything else you need.

Tony_OAS_Apex_User

Thank you Gianni, after I dropped it and tried again, it let me create with function.

I was doing with creating a view from database side and pre_pouplated the data. well it's good checking model that i can use.