select t1.col1,t1.col2, t2.col1, t2.col2, t2.col3
from openquery('BASE1', 'select code, name from oktmo where id_s=12') t1,
     openquery('BASE2', 'select s.CODE, d.ZN1, d.DATE1 from data_269 d, oktmo s where d.DATE1=''22.08.2017'' and d.OKTMO=s.ID') t2
where t1.col1=t2.col1

CREATE PROCEDURE OPENQUERY(
    LINKED_RDB VARCHAR(30),
    LINKED_SQL VARCHAR(500))
RETURNS (
    COL1 VARCHAR(300),
    COL2 VARCHAR(300),
    COL3 VARCHAR(300),
    COL4 VARCHAR(300),
    COL5 VARCHAR(300),
    COL6 VARCHAR(300),
    COL7 VARCHAR(300),
    COL8 VARCHAR(300),
    COL9 VARCHAR(300),
    COL10 VARCHAR(300))
AS
declare variable SQL varchar(4000);
declare variable SQL1 varchar(4000);
declare variable SQL2 varchar(300);
declare variable SQL3 varchar(4000);
declare variable BASE_RDB varchar(30);
declare variable IDENT varchar(50);
declare variable TMP varchar(50);
declare variable FIELD_NAME varchar(30);
declare variable FIELD_TYPE varchar(50);
declare variable I integer;
begin
    ident = 'TMP_'||substring(replace(trim(uuid_to_char(gen_uuid())), '-','') from 1 for 10);
    select rdb$get_context('SYSTEM', 'DB_NAME') from rdb$database into :base_rdb;
    linked_rdb = reverse(substring(reverse(:base_rdb) from position('\' in reverse(:base_rdb))))||:linked_rdb||'.RDB';

    for execute statement 'select trim(RDB$RELATION_NAME) from RDB$RELATIONS where upper(trim(RDB$RELATION_NAME)) like ''TMP%VIEW'''
    ON EXTERNAL :linked_rdb
    WITH autonomous transaction
    into :tmp
    do
    begin
        sql = 'drop view '||:tmp;
        EXECUTE STATEMENT :sql ON EXTERNAL :linked_rdb WITH AUTONOMOUS TRANSACTION;
        when any do i = i + 1;
    end

    sql = 'create view '||:ident||'_VIEW as '||:linked_sql;
    EXECUTE STATEMENT :sql ON EXTERNAL :linked_rdb WITH AUTONOMOUS TRANSACTION;

    for select trim(RDB$PROCEDURE_NAME) from RDB$PROCEDURES where upper(trim(RDB$PROCEDURE_NAME)) like 'TMP%PROC' or upper(trim(RDB$PROCEDURE_NAME)) like 'TMP%PROC2'
    into :tmp
    do
    begin
        sql = 'drop procedure '||:tmp;
        EXECUTE STATEMENT :sql WITH AUTONOMOUS TRANSACTION;
        when any do i = i + 1;
    end

    sql = 'create procedure '||:ident||'_PROC
RETURNS (
    FIELD_NAME varchar(30),
    FIELD_TYPE varchar(50),
    FIELD_NULL varchar(10),
    ORD integer)
as
begin
for EXECUTE STATEMENT ''SELECT
TRIM(RF.RDB$FIELD_NAME) as FIELD_NAME,
CASE F.RDB$FIELD_TYPE
    WHEN 7 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN ''''SMALLINT''''
        WHEN 1 THEN ''''NUMERIC('''' || F.RDB$FIELD_PRECISION || '''', '''' || (-F.RDB$FIELD_SCALE) || '''')''''
        WHEN 2 THEN ''''DECIMAL('''' || F.RDB$FIELD_PRECISION || '''', '''' || (-F.RDB$FIELD_SCALE) || '''')''''
      END
    WHEN 8 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN ''''INTEGER''''
        WHEN 1 THEN ''''NUMERIC(''''  || F.RDB$FIELD_PRECISION || '''', '''' || (-F.RDB$FIELD_SCALE) || '''')''''
        WHEN 2 THEN ''''DECIMAL(''''  || F.RDB$FIELD_PRECISION || '''', '''' || (-F.RDB$FIELD_SCALE) || '''')''''
      END
    WHEN 9 THEN ''''QUAD''''
    WHEN 10 THEN ''''FLOAT''''
    WHEN 12 THEN ''''DATE''''
    WHEN 13 THEN ''''TIME''''
    WHEN 14 THEN ''''CHAR('''' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || '''') ''''
    WHEN 16 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN ''''BIGINT''''
        WHEN 1 THEN ''''NUMERIC('''' || F.RDB$FIELD_PRECISION || '''', '''' || (-F.RDB$FIELD_SCALE) || '''')''''
        WHEN 2 THEN ''''DECIMAL('''' || F.RDB$FIELD_PRECISION || '''', '''' || (-F.RDB$FIELD_SCALE) || '''')''''
      END
    WHEN 27 THEN ''''DOUBLE''''
    WHEN 35 THEN ''''TIMESTAMP''''
    WHEN 37 THEN ''''VARCHAR('''' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || '''')''''
    WHEN 40 THEN ''''CSTRING'''' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || '''')''''
    WHEN 45 THEN ''''BLOB_ID''''
    WHEN 261 THEN ''''BLOB SUB_TYPE '''' || F.RDB$FIELD_SUB_TYPE
    ELSE ''''RDB$FIELD_TYPE: '''' || F.RDB$FIELD_TYPE || ''''?''''
END FIELD_TYPE,
TRIM(IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, '''''''', ''''NOT NULL'''')) as FIELD_NULL,
RF.RDB$FIELD_POSITION as ORD
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
WHERE (UPPER(RF.RDB$RELATION_NAME) in (UPPER('''''||:ident||'_VIEW'''')) ) AND (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
ORDER BY RF.RDB$RELATION_NAME, RF.RDB$FIELD_POSITION''
ON EXTERNAL '''||:linked_rdb||'''
WITH autonomous transaction
into :FIELD_NAME, :FIELD_TYPE, :FIELD_NULL, :ORD
do SUSPEND;
end';
    EXECUTE STATEMENT :sql WITH AUTONOMOUS TRANSACTION;

    sql1 = '';
    sql2 = '';
    sql3 = '';
    i = 1;
    for execute statement 'select FIELD_NAME, FIELD_TYPE from '||:ident||'_PROC order by ORD'
    into :FIELD_NAME, :FIELD_TYPE
    do
    begin
        sql1 = :sql1||',col'||:i||' '||:field_type;
        sql2 = :sql2||',:col'||:i;
        sql3 = :sql3||',trim(cast('||:field_name||' as varchar(300)))';

        i = :i + 1;
        if (:i > 10) then break;
    end

    while (i < 11)
    do
    begin
        sql1 = :sql1||',col'||:i||' varchar(1)';
        sql2 = :sql2||',:col'||:i;
        sql3 = :sql3||',null as col'||:i;

        i = :i + 1;
    end

    sql = 'create procedure '||:ident||'_PROC2 RETURNS('||substring(:sql1 from 2)||')
as
begin
    for execute statement ''select '||substring(:sql3 from 2)||' from '||:ident||'_VIEW''
    ON EXTERNAL '''||:linked_rdb||'''
    WITH autonomous transaction
    into '||substring(:sql2 from 2)||'
    do SUSPEND;
end';

    EXECUTE STATEMENT :sql WITH AUTONOMOUS TRANSACTION;

    for execute statement 'select col1,col2,col3,col4,col5,col6,col7,col8,col9,col10 from '||:ident||'_PROC2'
    WITH autonomous transaction
    into :col1,:col2,:col3,:col4,:col5,:col6,:col7,:col8,:col9,:col10
    do SUSPEND;

    sql = 'execute block as begin if (exists(select * from RDB$RELATIONS where upper(RDB$RELATION_NAME)=upper('''||:ident||'_VIEW''))) then
           EXECUTE STATEMENT ''drop view '||:ident||'_VIEW'' WITH AUTONOMOUS TRANSACTION; END';
    EXECUTE STATEMENT :sql ON EXTERNAL :linked_rdb WITH AUTONOMOUS TRANSACTION;

    if (exists(select * from RDB$PROCEDURES where RDB$PROCEDURE_NAME=:ident||'_PROC')) then
    begin
        sql = 'drop procedure '||:ident||'_PROC';
        EXECUTE STATEMENT :sql WITH AUTONOMOUS TRANSACTION;
    end

end