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: 17 Aug 2006 12:32:02 -0700
Message-ID: <1155843122.448552.256100@75g2000cwc.googlegroups.com>

> I'm not convinced you can't do without the temporary table, but right
> now we still have insufficient information.

Ok, here's the most information that I can give. Here's what's going on as far as queries go:

CREATE TABLE quart_total_tmp_da1_&&4 (quart      VARCHAR2(13),
                                  emp_no     NUMBER(9),
                                  pick_line  NUMBER(3),
                                  begin_time DATE,
                                  cmpl_time  DATE,
                                  qty        NUMBER(9),
                                  wght       NUMBER(20,2)
                                 );

then populate with:

INSERT INTO quart_total_tmp_da1_&&4 ( quart,

                                      emp_no,
                                      pick_line,
                                      begin_time,
                                      cmpl_time,
                                      qty,
                                      wght)
SELECT '05h30 @ 7h14',
          a.act_emp_no,
          pl.line_id,
          td.act_begin,
          td.act_end,
          td.exp_qty,
          pq.unit_wght*td.exp_qty
FROM   clsd_task_detail td,
          clsd_task t,
          clsd_assign a,
          pickzone pz,
          pickline pl,
          prodqty pq,
          ordhead o,
          emp e

WHERE a.assign_type IN ('PF01', 'PR01') AND (('&&2' = 'SUCC' AND ((o.cust_no > '00922999' AND o.cust_no < '00924000') OR
          (o.cust_no > '00932999' AND o.cust_no < '00934000') OR
           o.cust_no in ('00900001','00900003','00900006')))
OR ('&&2' = 'DA' AND o.ord_type = 'DA') OR ('&&2' = 'TOUS'))
AND    td.ord_cntrl_no =  o.ord_cntrl_no
AND    td.task_no      =  t.task_no
AND    t.assign_no     =  a.assign_no
AND    td.pick_zone_no =  pz.pick_zone_no
AND    pz.line_id      =  pl.line_id
AND    td.act_qty      >  0
AND    td.act_qty      >= td.exp_qty

AND td.act_begin is not null
AND to_char(td.act_end,'YYYYMMDD') = DATEFIN AND td.act_end is not null
AND ((e.emp_name = '&&4' and e.emp_no = a.act_emp_no) OR
          ('&&4' = 'TOUS' AND e.emp_no = a.act_emp_no))
AND    pq.prod_no      =  td.exp_prod_no
AND    pq.uom          = '6'

AND to_char(td.act_end, 'HH24MISS') between '053000' and '071459' UNION ALL
   SELECT '05h30 @ 7h14',
          a.act_emp_no,
          pl.line_id,
          td.act_begin,
          td.act_end,
          td.exp_qty,
          pq.unit_wght*td.exp_qty
   FROM   aclsd_task_detail td,
          aclsd_task t,
          aclsd_assign a,
          pickzone pz,
          pickline pl,
          prodqty pq,
          aordhead o,
          emp e

   WHERE a.assign_type IN ('PF01', 'PR01')    AND (('&&2' = 'SUCC' AND ((o.cust_no > '00922999' AND o.cust_no < '00924000') OR
              (o.cust_no > '00932999' AND o.cust_no < '00934000') OR
              o.cust_no in ('00900001','00900003','00900006')))
    OR ('&&2' = 'DA' AND o.ord_type = 'DA')     OR ('&&2' = 'TOUS'))
   AND    td.ord_cntrl_no =  o.ord_cntrl_no
   AND    td.task_no      =  t.task_no
   AND    t.assign_no     =  a.assign_no
   AND    td.pick_zone_no =  pz.pick_zone_no
   AND    pz.line_id      =  pl.line_id
   AND    td.act_qty      >  0
   AND    td.act_qty      >= td.exp_qty

   AND td.act_begin is not null
   AND to_char(td.act_end,'YYYYMMDD') = DATEFIN    AND td.act_end is not null
   AND ((e.emp_name = '&&4' and e.emp_no = a.act_emp_no) OR
             ('&&4' = 'TOUS' AND e.emp_no = a.act_emp_no))
   AND    pq.prod_no      =  td.exp_prod_no
   AND    pq.uom          = '6'

   AND to_char(td.act_end, 'HH24MISS') between '053000' and '071459'

Take note that this is a part of the insert query. It's 800 lines long. The UNION ALL are the same except that the SELECT reflects other work shifts.

Then there is the other table creation:

CREATE TABLE emp_avg_da1_&&4 (emp_name,

                          tmp_moy,
                          nb_pick,
                          tmp_moy_caisse,
                          nb_caisse,
                          poids)

AS

   SELECT e.emp_name,

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 e.emp_name;

Then a 3rd table creation:

CREATE GLOBAL TEMPORARY 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;

And finally, the query for the report itself from the data contained in those tables:

SELECT to_char(sysdate,'YYYY-MM-DD') date1,

       rpad(to_char(sysdate,'hh24:mi'),9) heure1,
       quart qt,
       emp_name aa,
       pick_line gg,
       to_char(to_date(trunc(tmp_moy),'SSSSS'),'MI:SS') bb,
       nb_pick cc,
       to_char(to_date(trunc(tmp_moy_caisse),'SSSSS'),'MI:SS') dd,
       nb_caisse ee,
       poids ff

FROM quart_avg_da1_&&4
WHERE tmp_moy < 86400
ORDER by 3;

and:

SELECT to_char(sysdate,'YYYY-MM-DD') date1,

       rpad(to_char(sysdate,'hh24:mi'),9) heure1,
       emp_name aa,
       to_char(to_date(trunc(tmp_moy),'SSSSS'),'MI:SS') bb,
       nb_pick cc,
       to_char(to_date(trunc(tmp_moy_caisse),'SSSSS'),'MI:SS') dd,
       nb_caisse ee,
       poids ff

FROM emp_avg_da1_&&4
WHERE tmp_moy < 86400
ORDER BY 3; And then we finish with this:

DROP TABLE quart_avg_da1_&&4;
DROP TABLE emp_avg_da1_&&4;
DROP TABLE quart_total_tmp_da1_&&4;

That's what I'm trying to fix. I tried the GLOBAL TEMPORARY advice, but like previously said in the thread, I get erratic behaviours. That would have been the simplest solution in this case I think, but I can't use it.

I liked the refcursor idea, but I can't select from another refcursor it seems. It would have been great and fixed the main gripe you guys have with creating tables on the fly. As for the joins advice, with the huge queries that I have, I'm at a loss as where to start. Still working on it though.

Denis

P.S. Sorry about the query formatting. Got mangled by Google. Received on Thu Aug 17 2006 - 14:32:02 CDT

Original text of this message

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