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
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.
In that case I would definitely go for the opaque view. It's the "least bad" option.
Why not do a lookup in the RPD? Assuming you are dealing on the same grain level of 'reference' and 'event_id' ...
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.
Though given the OP is in snow flake territory already perhaps this is the least of his issues.