Problem mit UNION Select

[mkinzler]

Lösung 2 als SP:

SQL-Code:

SET TERM ^ ;

CREATE OR ALTER PROCEDURE "PLAN" (
    monat smallint,
    jahr integer)
returns (
    personalnr integer,
    "1" char(2),
    "2" char(2),
    ...
    "31" char(2))
as
declare variable tag smallint;
declare variable kurz char(2);
begin
  tag=0; kurz=''; "1" = ''; "2" = ''; ... "31" = '';
  for
    select
      distinct personalnr
    from
        temp2007 p into :personalnr do
    begin
       for
         select
             d.kurzzeichen, d.tag
         from
              temp2007 d
         where
             d.personalnr = :personalnr and
             monat=:monat and
             jahr = :jahr
         into
             kurz, tag do
         begin
            if (tag = 1) then "1" = :kurz;
            if (tag = 2) then "2" = :kurz;
            ...
            if (tag = 31) then "31" = :kurz;
         end
         suspend;
    end
end^

SET TERM ; ^

GRANT ALL ON TEMP2007 TO PROCEDURE "PLAN";

GRANT EXECUTE ON PROCEDURE "PLAN" TO SYSDBA;