Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Reporting with dynamic sql
> 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
(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
('&&4' = 'TOUS' AND e.emp_no = a.act_emp_no)) AND pq.prod_no = td.exp_prod_no AND pq.uom = '6'
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
(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
('&&4' = 'TOUS' AND e.emp_no = a.act_emp_no)) AND pq.prod_no = td.exp_prod_no AND pq.uom = '6'
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)
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
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)
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
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
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
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