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;

[mu8zeh]

Mein PC ist noch mit Lösung 1 beschäftigt und pfeift in höchsten Tönen
CPU Auslastung.

Fehlermeldung ist aber keine gekommen.

[mkinzler]

Deshalb eignet sich bei diesem Problem auch die Lösung mit der SP

[mu8zeh]

Lösung 1 hat leider nicht Funktioniert. Ich musste es Abbrechen. Anschließend war DB defect.

[mu8zeh]

Lösung 2?

Gehe ich richtig davon aus Lösung 2 als neu Procedures im IB Expert einzustellen?

[mkinzler]

Ja. am Besten Skript starten

[mu8zeh]

muss erst mal PC neu Booten.
Firebird-Server Startet nicht mehr. :coder2:

Ich melde mich morgen zu lösung 2

vielen Dank ersteinmal

[omata]

So nachdem du nun ordentliche Beispieldaten geliefert hast, habe ich mir das nochmal angesehen.

1. leg bitte in deinen Tabellen auch einen Primary Key an (benutze Contraints!)
2. benutze bitte adiquate Datentypen für deine Tabellenspalten, also für dein Datum auch den Typ Date
3. benutze immer für lange Texte den Typ VARCHAR, der verbraucht nur so viel Speicher wie benötigt wird.
CHAR benutzt immer die komplette Größe

Hier mal ein Vorschlag für deine Tabellenstruktur...

SQL-Code:

CREATE TABLE TEMP2007(
  DATUM DATE NOT NULL,
  PERSONALNR CHAR(4) NOT NULL,
  URLAUB CHAR(4),
  KURZZEICHEN CHAR(2) NOT NULL,
  MEMO CHAR(1),
  MEMOTEXT VARCHAR(250),
  AENDERUNG DATE,
  AENDERUNGVON CHAR(10),
  CONSTRAINT PK_TEMP2007_1 PRIMARY KEY (DATUM, PERSONALNR)
);

Dabei wird jetzt keine Tag-, Monat-, Jahr-Spalte mehr benötigt, da diese Daten aus dem Datum extrahiert werden können.
Stichwort: Redundanz!

Hier mal ein Vorschlag für deine gewünschte Auswertung...

SQL-Code:

SELECT EXTRACT(YEAR FROM datum) jahr, 
       EXTRACT(MONTH FROM datum) monat,
       personalnr, 
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 1  THEN kurzzeichen ELSE NULL END) AS t1,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 2  THEN kurzzeichen ELSE NULL END) AS t2,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 3  THEN kurzzeichen ELSE NULL END) AS t3,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 4  THEN kurzzeichen ELSE NULL END) AS t4,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 5  THEN kurzzeichen ELSE NULL END) AS t5,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 6  THEN kurzzeichen ELSE NULL END) AS t6,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 7  THEN kurzzeichen ELSE NULL END) AS t7,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 8  THEN kurzzeichen ELSE NULL END) AS t8,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 9  THEN kurzzeichen ELSE NULL END) AS t9,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 10 THEN kurzzeichen ELSE NULL END) AS t10,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 11 THEN kurzzeichen ELSE NULL END) AS t11,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 12 THEN kurzzeichen ELSE NULL END) AS t12,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 13 THEN kurzzeichen ELSE NULL END) AS t13,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 14 THEN kurzzeichen ELSE NULL END) AS t14,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 15 THEN kurzzeichen ELSE NULL END) AS t15,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 16 THEN kurzzeichen ELSE NULL END) AS t16,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 17 THEN kurzzeichen ELSE NULL END) AS t17,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 18 THEN kurzzeichen ELSE NULL END) AS t18,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 19 THEN kurzzeichen ELSE NULL END) AS t19,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 20 THEN kurzzeichen ELSE NULL END) AS t20,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 21 THEN kurzzeichen ELSE NULL END) AS t21,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 22 THEN kurzzeichen ELSE NULL END) AS t22,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 23 THEN kurzzeichen ELSE NULL END) AS t23,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 24 THEN kurzzeichen ELSE NULL END) AS t24,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 25 THEN kurzzeichen ELSE NULL END) AS t25,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 26 THEN kurzzeichen ELSE NULL END) AS t26,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 27 THEN kurzzeichen ELSE NULL END) AS t27,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 28 THEN kurzzeichen ELSE NULL END) AS t28,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 29 THEN kurzzeichen ELSE NULL END) AS t29,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 30 THEN kurzzeichen ELSE NULL END) AS t30,
       MAX(CASE WHEN EXTRACT(DAY FROM datum) = 31 THEN kurzzeichen ELSE NULL END) AS t31
FROM temp2007
GROUP BY EXTRACT(YEAR FROM datum), EXTRACT(MONTH FROM datum), personalnr
ORDER BY EXTRACT(YEAR FROM datum), EXTRACT(MONTH FROM datum), personalnr

Bedenke aber dabei, dass es pro Datum+Person jetzt nur noch eine Zeile geben kann, dies wird durch den angelegten Primary Key eingehalten.

Geänderte Datenbank habe ich angehängt.

Hoffe das hilft dir weiter...

Gruss
Thorsten

[mu8zeh]

Hallo Zusammen

DB ist wieder OK

ich habe Lösung 2 getestet.
Fehler es wird "SET TERM ^" angemeckert :gruebel:

in einer 2. DB habe ich die Struktur wie beschrieben geändert und den SQL Befehl ausgeführt.
Es kommt kein Fehler aber auch keine Daten.

Ich will aber nochmal Prüfen. Möglicherweise habe ich einen Fehler reingebastelt.

Ich melde mich gegen Abend noch einmal.

Vielen Dank bis dahin

[mkinzler]

Unter Nützliches->Skript ausführen

[mu8zeh]

Hee ich bin ganz toll Stolz auf Euch

Lösung 2 von mkinzler und Lösung von Thorsten Zeigen mir die Daten schon mal im Querformat an.
Einen Plan, wie ich das auf 2 Monate umsetzten kann hätte ich auch schon.

:firejump: :cheers:

Echt super
Beim nächsten mal werde ich gleich versuchen meine Fragen richtig darzustellen.

Ich kann die Frage ersteinmal als Beantwortet markieren.

Vielen, Vielen Dank nochmal :hello:

[mu8zeh]

Hallo mkinzler,

ein kleines Problem habe ich da doch noch gefunden.
Die Variable "kurz" bleibt gefüllt.
bsp.
beim 1. gefundenen Datensatz am 1 Tag = Kurzzeichen "T"
beim 2. gefundenen Datensatz am 1 Tag auch = Kurzzeichen "T" aber nur dann, wenn der wert gleich Null oder '' sein sollte.

wo müsste ich die Variable leeren?

[mkinzler]

Wo auch die anderen Variablen geleert werden also

SQL-Code:

d1 = ''; ... kurz = '':

[mu8zeh]

ups das habe ich übersehen :oops:

[mkinzler]

Man sollte es aber eher in die innere Schleife verschieben.

[mu8zeh]

ich versuche gerade mein glück

[omata]

Zitat:

Zitat von mu8zeh

...Zeigen mir die Daten schon mal im Querformat an.
Einen Plan, wie ich das auf 2 Monate umsetzten kann hätte ich auch schon.

Und das verstehe ich schon wieder nicht! (ich scheine einfach zu doof zu sein, sorry)

Mein Vorschlag ist doch auch für mehrere Monate ausführbar und auch eingrenzbar. Allerdings kommt dort für jede Person eine Zeile pro Monat/Jahr heraus. Du willst doch wohl nicht alle Monate als Spalten realisieren? Was willst du den mit 365 Spalten? Was machst du in 10 Jahren -> 3650 Spalten?

Naja, wie auch immer: ich verstehe dich nicht.

Gruss
Thorsten

[mu8zeh]

Hallo Thorsten

Nein Du hast alles super verstanden.

So wie Ihr die beiden Vorlagen erstellt habt ist es was ich wollte.
Ein kleines Problem habe ich aber bei der Anzeige auf Desktop.

Wenn Auflösung = 1024 x 768 ist, dann brauche ich nur einen Monat.
Ist aber Auflösung 1280 x 960 oder Monat = Februar, dann brauche ich zwei Monate.
Zum B. Aktuellen Monat und ca. die hälfte vom nächsten Monat.

Ich habe es noch nicht erstellt oder getestet. Aber ich denke mal, dass ich das mit zwei
DBGrids darstellen könnte.

Mit einem Monat geht es super. Konntest Du meiner Schilderung folgen?

[omata]

Ok, dann hier nochmal ein neuer Vorschlag...

Delphi-Quellcode:

uses DateUtils;

function GetSQL(StartTag:TDate; Monat:byte):string;
var s:string;
    Datum, Ende:TDate;
begin
  Datum:=StartTag;
  Ende:=IncMonth(StartTag, Monat)-1;
  s:='';
  while Datum <= Ende do begin
    s:=s + Format(
      '       MAX(CASE WHEN datum = %s ' +
                   'THEN kurzzeichen ' +
                   'ELSE NULL ' +
                 'END) AS "%s",',
      [QuotedStr(DateToStr(Datum)), DateToStr(Datum)]) + #13;
    Datum:=IncDay(Datum, 1);
  end;
  if s <> '' then
    s:=copy(s, 1, length(s)-2);
  Result:=
    Format(
      'SELECT EXTRACT(YEAR FROM datum) jahr,'#13 +
      '       EXTRACT(MONTH FROM datum) monat,'#13 +
      '       personalnr,'#13 +
      '%s'#13 +
      'FROM temp2007'#13 +
      'WHERE datum BETWEEN %s AND %s'#13 +
      'GROUP BY EXTRACT(YEAR FROM datum),'#13 +
      '         EXTRACT(MONTH FROM datum), personalnr'#13 +
      'ORDER BY EXTRACT(YEAR FROM datum),'#13 +
      '         EXTRACT(MONTH FROM datum), personalnr',
      [s,
       QuotedStr(DateToStr(StartTag)),
       QuotedStr(DateToStr(Ende))]
    );
end;

Aufruf...

Ein Monat:

Delphi-Quellcode:

Query.SQL.Text:=GetSQL(StrToDate('1.5.2008'), 1);

Zwei Monate:

Delphi-Quellcode:

Query.SQL.Text:=GetSQL(StrToDate('1.5.2008'), 2);

Halb + Ganz + Halb:

Delphi-Quellcode:

Query.SQL.Text:=GetSQL(StrToDate('15.4.2008'), 2);

Gruss
Thorsten

[mu8zeh]

Hallo Thorsten

vielen Dank. Ich werde es morgen einbauen und Rückinfo geben.

Ach ich konnte die Zeit nicht abwarten.
Was soll ich sagen. Ich bin sprachlos.
Ich entnehme aber daraus, dass Du etwas mit meiner Beschreibung anfangen konntest. :roll:

Nun wird es ein leichtes sein, die Daten entsprechend anzuzeigen.
Vielen Dank noch mal an alle die mir geholfen haben. :mrgreen:

Grüße
Detlef

[mu8zeh]

Eine Frage hätte ich da mal noch.

Was macht das „%“ Zeichen?
Möchte gerne Verstehen, was ich in das Prog. Schreibe

[mkinzler]

% ist der SQL-Jocker. Also wie ein * im Dateinamen

[omata]

Zitat:

Zitat von mu8zeh

Was macht das „%“ Zeichen?

Zitat:

Zitat von mkinzler

% ist der SQL-Jocker. Also wie ein * im Dateinamen

@mkinzler: das ist richtig, aber ich glaube es war etwas anders gemeint


Ich habe in Delphi den Befehl Format benutzt, dort kannst du in einem String Parameter einfügen und setzen lassen. Schau einfach mal in die Delphihilfe.

Beispiel:

Delphi-Quellcode:

Parameter:='B'
s:=Format('A%sC', [Parameter]); 
Ausgabe: s = ABC

Gruss
Thorsten