Comments (2)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 AlexLevy commented Permalink

A model of clear explanation. Thank you very much Serge!

2 axxa commented Permalink

hi man,
ty for the explanation below, but i have a problem i cannot resolve with this, i need a store procedure that loop in a table that returns a name of a table in the db and depending from that name i need to do a select statement from the named table. i have tried doing it with an 'execute immediate' in so many ways that a lost the count here is an example of the execute immediate:

 
set insertstring = 'INSERT INTO pribpm.TEMP_T_TOQUE_CICLO (idSemana,tiempo_ciclo,tiempo_toque) SELECT to_number(to_char( '''|| ' time_stamp ' ||''' ,' || ' IW ' || ')) ,SUM(KPITOTALTIMECLOCK),SUM(s.KPIEXECUTIONTIMECLOCK) FROM ' || TABLA || ' where to_number(to_char( '''|| ' time_stamp ' ||''' ,' || ' IW ' || ')) between ' || (to_number(to_char(FECHA,'IW'))-3) || ' and ' || to_number(to_char(FECHA,'IW')) || ' GROUP BY to_number(to_char('''|| ' time_stamp ' ||''' ,' || ' IW ' || '))';
PREPARE stmt FROM insertstring;
EXECUTE IMMEDIATE insertstring;
---------------
 
where tabla is a string that contains the name of the table and fecha is a date in timestamp type
 
 
besides ive tried it with cursors like this
 
set select_ = 'SELECT time_stamp, KPITOTALTIMECLOCK, KPIEXECUTIONTIMECLOCK FROM ' || tabla;
PREPARE stmt FROM select_;
FOR v2 AS
c2 cursor for
execute select_
do
if to_number(to_char(time_stamp,'IW')) between (to_number(to_char(fecha,'IW'))-3) and to_number(to_char(fecha,'IW')) then
 
end if;
END FOR;
 
but with no succes.
 
may you or may someone please help me clear my error or giving some other idea about this im trying to do?
 
thank you