Sub-query in the select of OBIEE

Question

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

Robert Angel · Answer

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.

Thomas Dodds · Answer

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

[Deleted User] · Answer

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