Skip to Content
author's profile photo Former Member
Former Member

Order of Queries

Hi All,

To ensure data consistency in our Oracle data warehouse, it is set up to use flashback while the ETL processes are running. To invoke this, we have a class in the universe we can query and invokes a package procedure which sets flashback mode if needed. Obviously, this needs to be done before the main query occurs as flashback is set for the session. Is there a way of ensuring which query is performed first? Is it the first query left to right? Is it queries which return values for the page header? Or perhaps queries with content in the main report block? Can a query be forced to run before another query?

Thanks

Malcolm

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Feb 08, 2012 at 03:53 PM

    HI Malcom,

    As far as Query Sequance is concern I think we can can't enforce the sequence.

    Also have a look into below forum topic

    Can we force queries to execute in Sequence?

    But for TAble order we have one one parameter in Prm which handled how your query to be executed.

    *Table weight * By default BusinessObjects sorts the tables from the lighter to the heavier tables (those with the least amount of rows to those with the most). This determines the table order.

    Edited by: srrachna on Feb 8, 2012 4:55 PM

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      That'svery interesting. The query effectively is

      select package_name.procedure_name('MY_UNIVERSE')

      from dual.

      Calling this procedure checks if flashback is needed - i.e. if the relevant ETL process is ongoing and sets it if needed.

      It returns a date/time value to say when the data is valid up to - either the end of the last ETL process, or the start of the flashback if that is invoked.

      I'd considered some sort of query filter in my "main" query that could reference a value returned from this query, so that it would force this one to be done first, but can't think of anything relevant.

      I suppose you can't get much "lighter" in terms of rows returned than sys.dual!

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.