Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

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

Does not repository variables and subquerys go together?

Received Response
71
Views
2
Comments
3578722
3578722 Rank 1 - Community Starter
edited Aug 13, 2024 5:51PM in Oracle Analytics Forum

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

Answers

  • Martin van Donselaar
    Martin van Donselaar Rank 6 - Analytics & AI Lead

    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

  • 3578722
    3578722 Rank 1 - Community Starter

    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