Problem mit UNION Select

[mkinzler]

Schau mal #38

[mu8zeh]

nun komm ich garnet mehr weiter #38

[DeddyH]

Er meint hier

[mkinzler]

Sie Screenshoots

[mu8zeh]

Vielen Dank hab's geschnallt.

/************************************************** ****************************/
/**** Generated by IBExpert 2005.02.14 07.05.2008 19:27:05 ****/
/************************************************** ****************************/

SET SQL DIALECT 3;

SET NAMES ASCII;

SET CLIENTLIB 'C:\WINDOWS\system32\fbclient.dll';

CREATE DATABASE 'C:\Daten\Entwik\SPlaner_Code\DB\PLANER.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ASCII;



/************************************************** ****************************/
/**** Generators ****/
/************************************************** ****************************/

CREATE GENERATOR GEN_KATEGORIE_ID;
SET GENERATOR GEN_KATEGORIE_ID TO 22;

CREATE GENERATOR IBE$LOG_TABLES_GEN;
SET GENERATOR IBE$LOG_TABLES_GEN TO 607;



SET TERM ^ ;



/************************************************** ****************************/
/**** Stored Procedures ****/
/************************************************** ****************************/

CREATE PROCEDURE SP_GEN_KATEGORIE_ID
RETURNS (
ID INTEGER)
AS
BEGIN
EXIT;
END^



SET TERM ; ^


/************************************************** ****************************/
/**** Tables ****/
/************************************************** ****************************/



CREATE TABLE IBE$LOG_BLOB_FIELDS (
LOG_TABLES_ID NUMERIC(18,0) NOT NULL,
FIELD_NAME VARCHAR(67) CHARACTER SET UNICODE_FSS NOT NULL,
OLD_CHAR_VALUE VARCHAR(10000) CHARACTER SET UNICODE_FSS,
NEW_CHAR_VALUE VARCHAR(10000) CHARACTER SET UNICODE_FSS,
OLD_BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 80,
NEW_BLOB_VALUE BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

CREATE TABLE IBE$LOG_FIELDS (
LOG_TABLES_ID NUMERIC(18,0) NOT NULL,
FIELD_NAME VARCHAR(67) CHARACTER SET UNICODE_FSS NOT NULL,
OLD_VALUE VARCHAR(255) CHARACTER SET UNICODE_FSS,
NEW_VALUE VARCHAR(255) CHARACTER SET UNICODE_FSS
);

CREATE TABLE IBE$LOG_KEYS (
LOG_TABLES_ID NUMERIC(18,0) NOT NULL,
KEY_FIELD VARCHAR(67) CHARACTER SET UNICODE_FSS NOT NULL,
KEY_VALUE VARCHAR(255) CHARACTER SET UNICODE_FSS
);

CREATE TABLE IBE$LOG_TABLES (
ID NUMERIC(18,0) NOT NULL,
TABLE_NAME VARCHAR(67) CHARACTER SET UNICODE_FSS NOT NULL,
OPERATION VARCHAR(1) NOT NULL,
DATE_TIME TIMESTAMP NOT NULL,
USER_NAME VARCHAR(67) NOT NULL
);

CREATE TABLE KATEGORIE (
ID INTEGER,
BEZEICHNUNG CHAR(30),
KURZZEICHEN CHAR(3),
COLOR CHAR(15),
SU CHAR(1),
MO CHAR(1),
TU CHAR(1),
WE CHAR(1),
TH CHAR(1),
FR CHAR(1),
SA CHAR(1),
HD CHAR(1)
);

CREATE TABLE TEMP2007 (
DATUM CHAR(10),
TAG CHAR(2),
MONAT CHAR(2),
JAHR CHAR(4),
PERSONALNR CHAR(4),
URLAUB CHAR(4),
KURZZEICHEN CHAR(2),
MEMO CHAR(1),
MEMOTEXT CHAR(250),
AENDERUNG DATE,
AENDERUNGVON CHAR(10)
);



/************************************************** ****************************/
/**** Primary Keys ****/
/************************************************** ****************************/

ALTER TABLE IBE$LOG_TABLES ADD PRIMARY KEY (ID);


/************************************************** ****************************/
/**** Indices ****/
/************************************************** ****************************/

CREATE INDEX IBE$LOG_BLOB_FIELDS_IDX1 ON IBE$LOG_BLOB_FIELDS (LOG_TABLES_ID);
CREATE INDEX IBE$LOG_FIELDS_IDX1 ON IBE$LOG_FIELDS (LOG_TABLES_ID);
CREATE INDEX IBE$LOG_KEYS_IDX1 ON IBE$LOG_KEYS (LOG_TABLES_ID);


/************************************************** ****************************/
/**** Triggers ****/
/************************************************** ****************************/


SET TERM ^ ;


/************************************************** ****************************/
/**** Triggers for tables ****/
/************************************************** ****************************/



/* Trigger: IBE$LOG_TABLES_BD */
CREATE TRIGGER IBE$LOG_TABLES_BD FOR IBE$LOG_TABLES
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
DELETE FROM IBE$LOG_FIELDS WHERE LOG_TABLES_ID = OLD.ID;
DELETE FROM IBE$LOG_BLOB_FIELDS WHERE LOG_TABLES_ID = OLD.ID;
DELETE FROM IBE$LOG_KEYS WHERE LOG_TABLES_ID = OLD.ID;
END^

/* Trigger: KATEGORIE_BI */
CREATE TRIGGER KATEGORIE_BI FOR KATEGORIE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GEN_KATEGORIE_ID,1);
END^

SET TERM ; ^



/************************************************** ****************************/
/**** Stored Procedures ****/
/************************************************** ****************************/


SET TERM ^ ;

ALTER PROCEDURE SP_GEN_KATEGORIE_ID
RETURNS (
ID INTEGER)
AS
BEGIN
ID = GEN_ID(GEN_KATEGORIE_ID, 1);
SUSPEND;
END
^


SET TERM ; ^

[mkinzler]

Setz bitte noch den Haken bei Datentabellen, so das auch die Tabelleninhalte exportiert werden.

[mu8zeh]

Anbei die TestDB
Hoffe, dass das weiterhilft.

[mkinzler]

Lösung 1 Joins:

SQL-Code:

select
    distinct p.personalnr,
    d1.kurzzeichen as "1",
    d2.kurzzeichen as "2",
    ...
    d31.kurzzeichen as "31"
from
    temp2007 p
        join temp2007 d1 on d1.personalnr = p.personalnr and d1.tag = 1
        join temp2007 d2 on d2.personalnr = p.personalnr and d2.tag = 2
        ...
        join temp2007 d31 on d31.personalnr = p.personalnr and d31.tag = 31;

[mu8zeh]

bin am Testen der Lösung 1

[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;