sp lässt sich nicht erstellen

[sancho1980]

ah, ok, danke ...

und jetz schon das nächste problem: kann es sein, dass ich "union" in einer sp nicht verwenden kann?

jedenfalls funktioniert keins der beiden:

SQL-Code:

SET TERM ^ ;

CREATE PROCEDURE GET_NEXT_20_DICENTRIES_BY_ID (
    IDINPUT BIGINT)
RETURNS (
    DATUM DATE,
    AUT VARCHAR(3),
    ZSDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
    ZSSEM VARCHAR(80),
    UPDAUT VARCHAR(3),
    ZSQCODE VARCHAR(15),
    ZSPRGM VARCHAR(20),
    ASQCODE VARCHAR(15),
    ZSVERW VARCHAR(80),
    ZSABK VARCHAR(10),
    UPDDATUM DATE,
    ZSAUDIO VARCHAR(20),
    ASVERW VARCHAR(80),
    ZSTERM VARCHAR(80),
    ZSVIDEO VARCHAR(20),
    ZSABBILDUNG VARCHAR(20),
    ASVIDEO VARCHAR(20),
    ASABBILDUNG VARCHAR(20),
    PROJ VARCHAR(20),
    ASDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
    ASAUDIO VARCHAR(20),
    REV VARCHAR(1),
    ASSEM VARCHAR(80),
    ASPRGM VARCHAR(20),
    ASABK VARCHAR(10),
    ASTERM VARCHAR(80),
    ID BIGINT)
AS
begin
  for
    select first 20 d.id, d.asterm,  d.asabk, d.asprgm, d.assem, d.zsterm,
    d.zsabk, d.zsprgm, d.zssem, d.datum, d.proj, d.rev, d.upddatum, d.asdef,
    d.zsdef, d.asaudio, d.asvideo, d.asabbildung, d.zsabbildung, d.zsaudio,
    d.zsvideo, asmain.asterm, zsmain.zsterm, a.aut, ua.aut,
    aslit.qcode, zslit.qcode
    from dicentries d
    left join dicentries asmain on d.asverw = asmain.id
    left join dicentries zsmain on d.zsverw = zsmain.id
    left join aut a on d.aut = a.id
    left join aut ua on d.updaut = ua.id
    left join lit aslit on d.asqcode = aslit.id
    left join lit zslit on d.zsqcode = zslit.id
    where d.id > :idinput order by d.id
    into :id, :asterm, :asabk, :asprgm, :assem, :zsterm, :zsabk, :zsprgm,
    :zssem, :datum, :proj, :rev, :upddatum, :asdef, :zsdef, :asaudio, :asvideo,
    :asabbildung, :zsabbildung, :zsaudio, :zsvideo, :asverw, :zsverw, :aut,
    :updaut, :asqcode, :zsqcode

    union

    select first 20 d.id, d.asterm,  d.asabk, d.asprgm, d.assem, d.zsterm,
    d.zsabk, d.zsprgm, d.zssem, d.datum, d.proj, d.rev, d.upddatum, d.asdef,
    d.zsdef, d.asaudio, d.asvideo, d.asabbildung, d.zsabbildung, d.zsaudio,
    d.zsvideo, asmain.asterm, zsmain.zsterm, a.aut, ua.aut,
    aslit.qcode, zslit.qcode
    from dicentries d
    left join dicentries asmain on d.asverw = asmain.id
    left join dicentries zsmain on d.zsverw = zsmain.id
    left join aut a on d.aut = a.id
    left join aut ua on d.updaut = ua.id
    left join lit aslit on d.asqcode = aslit.id
    left join lit zslit on d.zsqcode = zslit.id
    where d.id < :idinput order by d.id
    into :id, :asterm, :asabk, :asprgm, :assem, :zsterm, :zsabk, :zsprgm,
    :zssem, :datum, :proj, :rev, :upddatum, :asdef, :zsdef, :asaudio, :asvideo,
    :asabbildung, :zsabbildung, :zsaudio, :zsvideo, :asverw, :zsverw, :aut,
    :updaut, :asqcode, :zsqcode


   do
  suspend;
end^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE GET_NEXT_20_DICENTRIES_BY_ID TO SYSDBA;

SET TERM ^ ;

SQL-Code:

CREATE PROCEDURE GET_NEXT_20_DICENTRIES_BY_ID (
    IDINPUT BIGINT)
RETURNS (
    DATUM DATE,
    AUT VARCHAR(3),
    ZSDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
    ZSSEM VARCHAR(80),
    UPDAUT VARCHAR(3),
    ZSQCODE VARCHAR(15),
    ZSPRGM VARCHAR(20),
    ASQCODE VARCHAR(15),
    ZSVERW VARCHAR(80),
    ZSABK VARCHAR(10),
    UPDDATUM DATE,
    ZSAUDIO VARCHAR(20),
    ASVERW VARCHAR(80),
    ZSTERM VARCHAR(80),
    ZSVIDEO VARCHAR(20),
    ZSABBILDUNG VARCHAR(20),
    ASVIDEO VARCHAR(20),
    ASABBILDUNG VARCHAR(20),
    PROJ VARCHAR(20),
    ASDEF BLOB SUB_TYPE 1 SEGMENT SIZE 4096,
    ASAUDIO VARCHAR(20),
    REV VARCHAR(1),
    ASSEM VARCHAR(80),
    ASPRGM VARCHAR(20),
    ASABK VARCHAR(10),
    ASTERM VARCHAR(80),
    ID BIGINT)
AS
begin
  for
    select first 20 d.id, d.asterm,  d.asabk, d.asprgm, d.assem, d.zsterm,
    d.zsabk, d.zsprgm, d.zssem, d.datum, d.proj, d.rev, d.upddatum, d.asdef,
    d.zsdef, d.asaudio, d.asvideo, d.asabbildung, d.zsabbildung, d.zsaudio,
    d.zsvideo, asmain.asterm, zsmain.zsterm, a.aut, ua.aut,
    aslit.qcode, zslit.qcode
    from dicentries d
    left join dicentries asmain on d.asverw = asmain.id
    left join dicentries zsmain on d.zsverw = zsmain.id
    left join aut a on d.aut = a.id
    left join aut ua on d.updaut = ua.id
    left join lit aslit on d.asqcode = aslit.id
    left join lit zslit on d.zsqcode = zslit.id
    where d.id > :idinput order by d.id

    union

    select first 20 d.id, d.asterm,  d.asabk, d.asprgm, d.assem, d.zsterm,
    d.zsabk, d.zsprgm, d.zssem, d.datum, d.proj, d.rev, d.upddatum, d.asdef,
    d.zsdef, d.asaudio, d.asvideo, d.asabbildung, d.zsabbildung, d.zsaudio,
    d.zsvideo, asmain.asterm, zsmain.zsterm, a.aut, ua.aut,
    aslit.qcode, zslit.qcode
    from dicentries d
    left join dicentries asmain on d.asverw = asmain.id
    left join dicentries zsmain on d.zsverw = zsmain.id
    left join aut a on d.aut = a.id
    left join aut ua on d.updaut = ua.id
    left join lit aslit on d.asqcode = aslit.id
    left join lit zslit on d.zsqcode = zslit.id
    where d.id < :idinput order by d.id
    into :id, :asterm, :asabk, :asprgm, :assem, :zsterm, :zsabk, :zsprgm,
    :zssem, :datum, :proj, :rev, :upddatum, :asdef, :zsdef, :asaudio, :asvideo,
    :asabbildung, :zsabbildung, :zsaudio, :zsvideo, :asverw, :zsverw, :aut,
    :updaut, :asqcode, :zsqcode


   do
  suspend;
end^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE GET_NEXT_20_DICENTRIES_BY_ID TO SYSDBA;