Re: Reporting with dynamic sql

From: Deltones <vibroverb_at_hotmail.com>
Date: 16 Aug 2006 12:20:24 -0700
Message-ID: <1155756024.623102.274190@i3g2000cwc.googlegroups.com>

• Stuff snipped for brevity ----- Sybrand Bakker wrote:
  > creating 'temporary tables' on the fly has 'bad idea' inscribed all
  > over it. This is Oracle. In Oracle you *don't* need temporary tables.
  >
  > What you need is a REF CURSOR.
  >
  > Like this
  >
  > variable cur REF CURSOR
  > begin
  > open :cur for
  > '<your query *without temporary tables* here';
  > end;
  > /
  > print cur
  

Allright, I reached another wall. In the report I'm trying to fix, 3 temp tables are created. I'm trying to find a way to convert the script by using ref cursor instead, like Sybrand stated above. My problem is that one of the temp table is created using data from another temp table. This is from the script I'm working on:

CREATE TABLE quart_avg_da1_&&4 (quart,

                            emp_name,
                            pick_line,
                            tmp_moy,
                            nb_pick,
                            tmp_moy_caisse,
                            nb_caisse,
                            poids)

AS

   SELECT p.quart,
          e.emp_name,
          p.pick_line,

avg(decode(sign(p.cmpl_time-p.begin_time),-1,p.cmpl_time+86399-p.begin_time,p.cmpl_time-p.begin_time)) * 60 * 60 * 24,

          count(*),

(sum(decode(sign(p.cmpl_time-p.begin_time),-1,p.cmpl_time+86399-p.begin_time,p.cmpl_time-p.begin_time))/sum(qty)) * 60 * 60 * 24,

          sum(qty),
          sum(wght)/1000
   FROM   quart_total_tmp_da1_&&4 p,
          emp e

   WHERE (e.emp_name = '&&4' or '&&4' = '$' or '&&4' = 'TOUS')    AND e.emp_no = p.emp_no

   GROUP BY p.quart,
            e.emp_name,
            p.pick_line;

The other temp table is quart_total_tmp_da1_&&4 as you can see in the FROM section(&&4 would be the employee code for example). Now, select <fields> from <table_name> I know. But I'm not so sure that SELECT <fields> FROM <ref cursor> is gonna work. In fact, I just tried it, and it doesn't. What would be the best way to proceed then? By the way, sorry for the formatting of the query. Got kinda mangled by google. Received on Wed Aug 16 2006 - 14:20:24 CDT