Este es el codigo completo:
Código SQL
[-]CREATE OR ALTER PROCEDURE SP_AGN_PERF ( pdatefrom varchar(8), pdateto varchar(8), ptimefrom varchar(6), ptimeto varchar(6), psectorid integer) returns ( agentfullname varchar(30), agentid varchar(10), skill smallint, i_received smallint, i_answered smallint, i_short smallint, i_returned smallint, i_talktime char(10), logintime char(10), idletime char(10), oreceived smallint, oanswered smallint, oshort smallint, oreturned smallint, otalktime char(10), userid integer) as declare variable script1 varchar(5000); declare variable tmpagentid varchar(10); declare variable lwhere varchar(1024); declare variable tmplogin integer; declare variable tmpidle integer; declare variable tmp_i_talk integer; declare variable tmp_o_talk integer; declare variable scrpt varchar(100); begin -- Inicializa las vari agentfullname = ''; agentid = ''; skill = 0; i_received = 0; i_answered = 0; i_short = 0; i_returned = 0; i_talktime = '00:00:00'; logintime = '00:00:00'; idletime = '00:00:00'; o_received = 0; o_answered = 0; o_short = 0; o_returned = 0; o_talktime = '00:00:00'; userid = 0; LWHERE = 'logdate between '''|| pdatefrom ||''' and '''||pdateto ||''' ' || 'and logtime between '||ptimefrom||' and '||ptimeto; if ((P_SECTORID is not NULL) and (P_SECTORID > 0)) then LWHERE = :LWHERE||' and sectorid = '||:P_SECTORID; script1 = 'select userid, agentid, skill, ' || 'Sum(Case when calltype = ''I'' then 1 else 0 end), ' || 'Sum(Case when calltype = ''I'' and talktime > 0 then 1 else 0 end), ' || 'Sum(Case when calltype = ''I'' and talktime between 1 and 20 then 1 else 0 end), ' || 'Sum(Case when calltype = ''I'' and talktime = 0 and delaytime > 0 then 1 else 0 end), ' || 'Sum(Case when calltype = ''I'' then talktime + holdtime + cnsholdtime else 0 end), ' || 'Sum(Case when calltype = ''O'' then 1 else 0 end), ' || 'Sum(Case when calltype = ''O'' and talktime > 0 then 1 else 0 end), ' || 'Sum(Case when calltype = ''O'' and talktime between 1 and 20 then 1 else 0 end), ' || 'Sum(Case when calltype = ''O'' and talktime = 0 and delaytime > 0 then 1 else 0 end), ' || 'Sum(Case when calltype = ''O'' then talktime + holdtime + cnsholdtime else 0 end) ' || 'from logpos ' || 'where '||LWHERE|| ' group by userid, agentid, skill order by agentid, skill'; for execute statement script1 into :userid, :agentid, :skill, :i_received, :i_answered, :i_short, :i_returned, :tmp_i_talk, oreceived, oanswered, oshort, oreturned, :tmp_o_talk do begin select sum(interval_t), sum(idletime) from logposint where userid = :userid and (logdate between pdatefrom and pdateto) and (logtime between ptimefrom and ptimeto) into :tmplogin, :tmpidle; select * from sp_secsto_hhmmss(:tmplogin) into logintime; select * from sp_secsto_hhmmss(:tmpidle) into idletime; select * from sp_secsto_hhmmss(:tmp_i_talk) into i_talktime; select * from sp_secsto_hhmmss(:tmp_o_talk) into o_talktime; execute statement 'select user_fullname from users where userid = :userid' on external data source '192.168.x.x:sched' as user 'SYSDBA' password 'masterkey' into :agentfullname; suspend; end end
Espero encuentren donde esta el problema.
Gracias-