Joins in obiee RPD 11.1.1.9 ? — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Joins in obiee RPD 11.1.1.9 ?

Received Response
61
Views
6
Comments
SanthoshSreshta
SanthoshSreshta Rank 3 - Community Apprentice

Hi Expertise,

A newbie here for OBIEE

can we create a join as TableA.Business_date=TableA.Business AND TableA.Status='A' .???
When I am trying to do so, it is populating an error as nQSError: 30001 only columns,designated predicates and operators are allowed.

I want to join only the status having values as Y...

Please help me in doing this,

Thanks a million,

Santhosh S.

Answers

  • Hi,

    Of course you can, but you must use the "Expression Builder" and not write it directly in the Expression field of the physical join. Doing so it change the the kind of joins from Physical Foreign Key to Complex Join and will accept your extra condition on Status = 'A' .

  • SanthoshSreshta
    SanthoshSreshta Rank 3 - Community Apprentice

    Thanks for the Quick reply @Gianni Ceresa ,

    I have opened the Expression builder from BMM layer join. but where exactly I need to write the condition.?
    I could see only expression field inside builder.

  • It's not in BMM, it's in the physical layer ...

    In BMM you don't define any join expression, you just set the kind of join (inner, left / right / full outer).

    If you just want for a special need on your business model to join based on the condition you can also set it in the LTS itself and keep the generic join in the physical layer.

  • SanthoshSreshta
    SanthoshSreshta Rank 3 - Community Apprentice

    sorry to mention as BMM @Gianni Ceresa , I have went to Physical diagram->Expression Builder-Phyiscal join.. there when I am typing as status='Y' it is showing error..
    Can you please help me on where to go and write the condition?

  • Capture.PNG

    Just there

    In the expression builder you add your column by selecting it in the left panels and simply add your condition.

    Post a picture of your physical model and where are you trying to add the condition as it will make it easier to understand what happen exactly ...

  • SanthoshSreshta
    SanthoshSreshta Rank 3 - Community Apprentice

    Sorry for being late @Gianni Ceresa ,

    I have made it by applying at... expanding the source of the table->properties->content tab->where condition..

    Thanks for helping and enlightening me on complex joins. but anyhow my leads are not recommending to use complex join, so .. moved to above approach.

    thanks again.

    Santhosh S.