3 Replies Latest reply: Nov 19, 2012 9:54 AM by Jeff Smith Sqldev Pm-Oracle RSS

    Child reports and bind variables

    RoboMan
      I'm working on a user defined parent/child report. What I'm having an issue with is using a parents bind variable value in a child. To simpify my example:

      The master query is:

      select count(*), document
      from download_audit
      where activity_date > sysdate-:DAYS
      group by document

      which returns a count of downloads for each document in the last :DAYS days. I want the child report to then list the person that downloaded a selected document:

      select document, user_id, activity_date from download_audit
      where activity_date > sysdate - :DAYS
      and document=:DOCUMENT

      This does not work because the initial :DAYS bind isn't sent to the child.

      If I add the :DAYS as a selected column to the master sql it works. e.g.

      select count(*), document, :DAYS days
      from download_audit
      where activity_date > sysdate-:DAYS
      group by document

      My question is, do child bind variables all have to be selected columns in the master query or is there a way to have a "global" bind variable that gets carried from master to child?

      I'm using SQL Developer 3.2.20.09 on OS X Lion

      Edited by: RoboMan on Nov 16, 2012 11:23 AM