Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Reporting with dynamic sql

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>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US