External Data Souce
 

Hola amigos.
Tengo una SP en la que necesito extraer un dato que esta en otra BBDD.
Lo hago asi:

Código SQL [-]
execute statement 'select user_login from users where userid = :userid'
on external data source '192.168.x.x:sched'
as user 'SYSDBA'
password 'masterkey'
into :userlogin

Esto siempre me da el error "Cannot find table attach"
Aclaro que sched es el alias de la bbdd.

Alguna ayuda?
Gracias

Estimado, ¿ambas bases de datos están en la misma maquina?, de ser así ¿has probado poner la ruta completa a la base de datos? y por último aunque obvio el nombre de la tabla es el correcto?

Saludos.

Hola young, ambas bases estan en la misma pc y ya probe con el path y nada.
El nombre de la tabla es correcto.

Pon el código completo que lo veamos.

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-

Lo he humanizado un poco :p

Por cierto, te aconseja que "dividas y vencerás", porque eso así, a lo bruto, es inmasticable.

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

Encontré lo siguiente:

sql-statement, user, password, role and <connect-string> are string
expressions. When given directly, i.e. as literal strings, they must
be enclosed in single-quote characters.

Me imagino entonces que debería lucir mas o menos así...

Código SQL [-]
execute statement 'select user_fullname from users where userid = :userid on external data source ''192.168.x.x:sched'' 
                      as user ''SYSDBA'' password ''masterkey'''

Me gusta más así:

Código SQL [-]
execute statement 'select user_fullname from users where userid = ' || :userid || 'on external data source ''192.168.x.x:sched'' 
                      as user ''SYSDBA'' password ''masterkey'''

Saludos.

Young, en cualquiera de las dos formas compila y al ejecutar me da el error "Token unknown 'on'

Encontre el problema, fue culpa mia, el campo por el que hacia la consulta habia sido cambiado por otro y yo ni enterado.
Ahora funciona bien.
Gracias por la ayuda que me dieron.