Does not repository variables and subquerys go together?

Question

Hi,

I have a repository variable that is "YESTERDAY" (format yyyy-mm-dd). When I try to apply this to the analysis that I wrote myself in sql I get below error.

The field "invoice date" that I'm trying to filter on is from a subquery in the code, so might that be why I get this error?

State: HY000. Code: 23007. [nQSError: 23007] Atomic table reference cannot be mixed with derived column references in the same SELECT.

Thanks in advance!

Br

Johannes

3578722 · Answer

Thank you Martin, that was exactly what I needed! I feel kind of embarrassed that I did not think of it myself.

I would indeed prefer to fix the problem in the model itself but the problem is that I'm just a user of the tool and not an admin. My company buys this service and so the model is not within my reach.

Thanks again!

Br

Johannes

Martin van Donselaar · Answer

If you are using a direct database query anyway (I assume that's what you mean by query written i sql) why not calculate yesterday trunc(sysdate -1) in the query itself?

That being said, why are you using a Direct Sql Query? That should be used only as last resort.

What you should really do is model this properly in the repository which will enable you to use OBIEE to it's full potential