Sub-query in the select of OBIEE

Sunny86

Hello,

Trying to figure out how to add a sub-query in the select part of OBIEE generated query.

We have two tables which cannot be joined due to some modelling issues in snow flake schema set up.

We need to take one value from the second table using a sub-select.

Select Reference,Amount,Currency,(select additional reference from partpay where partpay.reference=fullpay.reference and partpay.event_id=fullpay.event_id) from fullpay where Filter set

[Deleted User]

You can't just "add a sub-query" that's not how OBI works as a technology.

Either solve the issue at its source or everything we can propose to you will not be a "solution" but a mere "hack" or "workaround". It will not solve the issue and you will pay in terms of maintenance, performance etc

Sunny86

Hello Christian,

Thanks for the response.

It is difficult to resolve at the source as there are 500s of existing reports with the same model. We can make an opaque view and join it in the rpd level to fetch the record or use DDR from answers. If something else can resolve this, it would be much better because most of the OBIEE features disabled for DDR request.

[Deleted User]

In that case I would definitely go for the opaque view. It's the "least bad" option.

Thomas Dodds

Why not do a lookup in the RPD?   Assuming you are dealing on the same grain level of 'reference' and 'event_id' ...

Robert Angel

I always think of lookups as being useful for translation purposes, whether that is turning a code in to a descriptor, or changing english into whatever other language, but it can be a life saver at the end of a long project when the client produces the 'must have' that was never specified, discussed or talked about previously and you can shoe horn an extra table worth of fields onto a dimension without a massive ETL job.

But, the 11th hour job can come at a price when that overlooked field becomes the thing that the end user is using to filter his reports on 9/10 times...  as it will not be as performant as fixing the issue via the ETL, particularly if it is sparse.

@Thomas Dodds putting this for the 'record' as I know you know this, but I don't know @3295595 and others who come after him will.

Though given the OP is in snow flake territory already perhaps this is the least of his issues.