file CONNECT (kern test!

 *++ ' Die folgenden Differenzen sind vier Kalender des aktuellen Monats.  '
 *++ ' Die erste Spalte ist die jeweilige Kalenderwoche mit den einzelnen  '
 *++ ' Wochentagen dahinter. Mitunter kann es sogar zu Unterschieden im    '
 *++ ' Resultcount kommen, da einige Monate sich auf 6 Wochen verteilen    '
 *++ ' und manche Februare nur 4 Wochen brauchen'!
* Erster Versuch mit einer Hilfstabelle
CREATE TABLE stat AS SELECT
    -DAYOFWEEK(SUBSTR(DATE,1,6)||'01')+1           c,
    DAY(SUBDATE(SUBSTR(CHR(YEAR(DATE)*100+(NUM(
        SUBSTR(DATE,5,2))MOD 12+1)),1,6)||'01',1)) d,
    WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')            w,
    TRIM(MONTHNAME(DATE))||' '||SUBSTR(DATE,1,4)   m
  FROM dual!

CREATE TABLE cal (
  kw   FIXED(2),
  mo   FIXED(2),
  di   FIXED(2),
  mi   FIXED(2),
  do   FIXED(2),
  fr   FIXED(2),
  sa   FIXED(2),
  so   FIXED(2)
)!

INSERT INTO cal (SELECT w, GREATEST (0, 1+c),
                           GREATEST (0, 2+c),
                           GREATEST (0, 3+c),
                           GREATEST (0, 4+c),
                           GREATEST (0, 5+c),
                           GREATEST (0, 6+c),
                           GREATEST (0, 7+c) FROM stat)!
INSERT INTO cal (SELECT DECODE (w+1, 53, 1, w+1),
    8+c,  9+c, 10+c, 11+c, 12+c, 13+c, 14+c FROM stat)!
INSERT INTO cal (SELECT DECODE (w+2, 54, 2, w+2),
   15+c, 16+c, 17+c, 18+c, 19+c, 20+c, 21+c FROM stat)!
INSERT INTO cal (SELECT DECODE (w+3, 55, 3, w+3),
   22+c, 23+c, 24+c, 25+c, 26+c, 27+c, 28+c FROM stat)!
INSERT INTO cal (SELECT DECODE (w+4, 56, 4, w+4), 
                             LEAST (d+1, 29+c),
                             LEAST (d+1, 30+c),
                             LEAST (d+1, 31+c),
                             LEAST (d+1, 32+c),
                             LEAST (d+1, 33+c),
                             LEAST (d+1, 34+c),
                             LEAST (d+1, 35+c) FROM stat)!
INSERT INTO cal (SELECT DECODE (w+5, 57, 5, w+5), LEAST (d+1, 36+c),
                             LEAST (d+1, 37+c),
                             d+1, d+1, d+1, d+1, d+1 FROM stat)!

SELECT ' *++ ' " *++ ",
       kw,
       DECODE (mo, 0, NULL, d+1, NULL, mo) mo,
       DECODE (di, 0, NULL, d+1, NULL, di) di,
       DECODE (mi, 0, NULL, d+1, NULL, mi) mi,
       DECODE (do, 0, NULL, d+1, NULL, do) do,
       DECODE (fr, 0, NULL, d+1, NULL, fr) fr,
       DECODE (sa, 0, NULL, d+1, NULL, sa) sa,
       DECODE (so, 0, NULL, d+1, NULL, so) so,
       DECODE (kw, w, m, '') "Month"
  FROM cal, stat
  WHERE mo BETWEEN 1 AND d
     OR di BETWEEN 1 AND d
     OR mi BETWEEN 1 AND d
     OR do BETWEEN 1 AND d
     OR fr BETWEEN 1 AND d
     OR sa BETWEEN 1 AND d
     OR so BETWEEN 1 AND d
  ORDER BY cal.so!
FETCH INTO :A!

ROLLBACK WORK!

*Zweiter Versuch mit einer View statt der Tabelle
CREATE VIEW stat AS SELECT
    -DAYOFWEEK(SUBSTR(DATE,1,6)||'01')+1           c,
    DAY(SUBDATE(SUBSTR(CHR(YEAR(DATE)*100+(NUM(
        SUBSTR(DATE,5,2))MOD 12+1)),1,6)||'01',1)) d,
    WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')            w,
    TRIM(MONTHNAME(DATE))||' '||SUBSTR(DATE,1,4)   m
  FROM dual!

CREATE VIEW cal (kw, mo, di, mi, do, fr, sa, so) AS
  SELECT w, GREATEST (0, 1+c),
            GREATEST (0, 2+c),
            GREATEST (0, 3+c),
            GREATEST (0, 4+c),
            GREATEST (0, 5+c),
            GREATEST (0, 6+c),
            GREATEST (0, 7+c) FROM stat
UNION ALL
  SELECT DECODE (w+1, 53, 1, w+1),  
         8+c,  9+c, 10+c, 11+c, 12+c, 13+c, 14+c FROM stat
UNION ALL
  SELECT DECODE (w+2, 54, 2, w+2), 
         15+c, 16+c, 17+c, 18+c, 19+c, 20+c, 21+c FROM stat
UNION ALL
  SELECT DECODE (w+3, 55, 3, w+3),
         22+c, 23+c, 24+c, 25+c, 26+c, 27+c, 28+c FROM stat
UNION ALL
  SELECT DECODE (w+4, 56, 4, w+4), 
              LEAST (d+1, 29+c),
              LEAST (d+1, 30+c),
              LEAST (d+1, 31+c),
              LEAST (d+1, 32+c),
              LEAST (d+1, 33+c),
              LEAST (d+1, 34+c),
              LEAST (d+1, 35+c) FROM stat
UNION ALL
  SELECT DECODE (w+5, 57, 5, w+5), 
              LEAST (d+1, 36+c),
              LEAST (d+1, 37+c),
              d+1, d+1, d+1, d+1, d+1 FROM stat!

SELECT ' *++ ' " *++ ",
       kw,
       DECODE (mo, 0, NULL, d+1, NULL, FIXED (mo, 2)) mo,
       DECODE (di, 0, NULL, d+1, NULL, FIXED (di, 2)) di,
       DECODE (mi, 0, NULL, d+1, NULL, FIXED (mi, 2)) mi,
       DECODE (do, 0, NULL, d+1, NULL, FIXED (do, 2)) do,
       DECODE (fr, 0, NULL, d+1, NULL, FIXED (fr, 2)) fr,
       DECODE (sa, 0, NULL, d+1, NULL, FIXED (sa, 2)) sa,
       DECODE (so, 0, NULL, d+1, NULL, FIXED (so, 2)) so,
       DECODE (kw, w, m, '') "Month"
  FROM cal, stat
  WHERE mo BETWEEN 1 AND d
     OR so BETWEEN 1 AND d
  ORDER BY cal.so!
FETCH INTO :A!
ROLLBACK WORK!

*Dritter Versuch: Ein einziges Select Statement mit einem FROM SELECT,
*                 das viele UNION ALLs enthaelt
SELECT ' *++ ' " *++ ",
       kw,
       DECODE (mo, 0, NULL, d+1, NULL, FIXED (mo, 2)) mo,
       DECODE (di, 0, NULL, d+1, NULL, FIXED (di, 2)) di,
       DECODE (mi, 0, NULL, d+1, NULL, FIXED (mi, 2)) mi,
       DECODE (do, 0, NULL, d+1, NULL, FIXED (do, 2)) do,
       DECODE (fr, 0, NULL, d+1, NULL, FIXED (fr, 2)) fr,
       DECODE (sa, 0, NULL, d+1, NULL, FIXED (sa, 2)) sa,
       DECODE (so, 0, NULL, d+1, NULL, FIXED (so, 2)) so,
       DECODE (kw, w, m, '') "Month"
  FROM 
       (SELECT WEEKOFYEAR (SUBSTR(DATE,1,6)||'01') kw,
               GREATEST (0, 1-c) mo,
               GREATEST (0, 2-c) di,
               GREATEST (0, 3-c) mi,
               GREATEST (0, 4-c) do,
               GREATEST (0, 5-c) fr,
               GREATEST (0, 6-c) sa,
               GREATEST (0, 7-c) so
          FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual) stat
      UNION ALL
        SELECT DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+1, 
                      53, 1, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+1),
               8-c, 9-c, 10-c, 11-c, 12-c, 13-c, 14-c
          FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual) stat
      UNION ALL
        SELECT DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+2, 
                      54, 2, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+2),
               15-c, 16-c, 17-c, 18-c, 19-c, 20-c, 21-c
          FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual) stat
      UNION ALL
        SELECT DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+3, 
                      55, 3, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+3),
               22-c, 23-c, 24-c, 25-c, 26-c, 27-c, 28-c
          FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual) stat
      UNION ALL
        SELECT DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+4, 
                      56, 4, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+4),
               LEAST (d+1, 29-c),
               LEAST (d+1, 30-c),
               LEAST (d+1, 31-c),
               LEAST (d+1, 32-c),
               LEAST (d+1, 33-c),
               LEAST (d+1, 34-c),
               LEAST (d+1, 35-c)
          FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1         c,
                       DAY(SUBDATE(SUBSTR(CHR(YEAR(DATE)*100+(NUM(
                       SUBSTR(DATE,5,2))MOD 12+1)),1,6)||'01',1))  d
                FROM dual) stat
      UNION ALL
        SELECT DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+5, 
                      57, 5, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+5),
               LEAST (d+1, 36-c),
               LEAST (d+1, 37-c),
               d+1, d+1, d+1, d+1, d+1
          FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1         c,
                       DAY(SUBDATE(SUBSTR(CHR(YEAR(DATE)*100+(NUM(
                       SUBSTR(DATE,5,2))MOD 12+1)),1,6)||'01',1))  d
                FROM dual) stat) cal,
       (SELECT -DAYOFWEEK(SUBSTR(DATE,1,6)||'01')+1         c,
               DAY(SUBDATE(SUBSTR(CHR(YEAR(DATE)*100+(NUM(
               SUBSTR(DATE,5,2))MOD 12+1)),1,6)||'01',1))   d,
               WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')          w,
               TRIM(MONTHNAME(DATE))||' '||SUBSTR(DATE,1,4) m
          FROM dual) stat
  WHERE mo BETWEEN 1 AND d
     OR so BETWEEN 1 AND d
  ORDER BY cal.so!
FETCH INTO :A!

*Vierter Versuch: Ein einziges Select Statement mit mehreren UNION ALLs,
*                 das alle jeweils ein FROM SELECT enthalten.
SELECT ' *++ ' " *++ ",
       WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')      kw,
       FIXED (DECODE (GREATEST (0, 1-c), 0, NULL, 1-c), 2) mo,
       FIXED (DECODE (GREATEST (0, 2-c), 0, NULL, 2-c), 2) di,
       FIXED (DECODE (GREATEST (0, 3-c), 0, NULL, 3-c), 2) mi,
       FIXED (DECODE (GREATEST (0, 4-c), 0, NULL, 4-c), 2) do,
       FIXED (DECODE (GREATEST (0, 5-c), 0, NULL, 5-c), 2) fr,
       FIXED (DECODE (GREATEST (0, 6-c), 0, NULL, 6-c), 2) sa,
                                           FIXED (7-c,  2) so,
       TRIM(MONTHNAME(DATE))||' '||SUBSTR(DATE,1,4) "Month"
  FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual)
UNION ALL
SELECT ' *++ ', DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+1, 
                  53, 1, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+1),
        FIXED ( 8-c, 2), FIXED ( 9-c, 2), FIXED (10-c, 2), FIXED (11-c, 2),
        FIXED (12-c, 2), FIXED (13-c, 2), FIXED (14-c, 2), ''
  FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual)
UNION ALL
SELECT ' *++ ', DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+2, 
                  54, 2, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+2),
       FIXED (15-c, 2), FIXED (16-c, 2), FIXED (17-c, 2), FIXED (18-c, 2),
       FIXED (19-c, 2), FIXED (20-c, 2), FIXED (21-c, 2), ''
  FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual)
UNION ALL
SELECT ' *++ ', DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+3, 
                  55, 3, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+3),
       FIXED (22-c, 2), FIXED (23-c, 2), FIXED (24-c, 2), FIXED (25-c, 2),
       FIXED (26-c, 2), FIXED (27-c, 2), FIXED (28-c, 2), ''
  FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1 c FROM dual)
UNION ALL
SELECT ' *++ ', DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+4, 
                  56, 4, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+4),
       FIXED (DECODE (LEAST (d+1, 29-c), d+1, NULL, 29-c), 2),
       FIXED (DECODE (LEAST (d+1, 30-c), d+1, NULL, 30-c), 2),
       FIXED (DECODE (LEAST (d+1, 31-c), d+1, NULL, 31-c), 2),
       FIXED (DECODE (LEAST (d+1, 32-c), d+1, NULL, 32-c), 2),
       FIXED (DECODE (LEAST (d+1, 33-c), d+1, NULL, 33-c), 2),
       FIXED (DECODE (LEAST (d+1, 34-c), d+1, NULL, 34-c), 2),
       FIXED (DECODE (LEAST (d+1, 35-c), d+1, NULL, 35-c), 2), ''
 FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1         c,
              DAY(SUBDATE(SUBSTR(CHR(YEAR(DATE)*100+(NUM(
              SUBSTR(DATE,5,2))MOD 12+1)),1,6)||'01',1))  d
         FROM dual)
UNION ALL
SELECT ' *++ ', DECODE(WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+5, 
                  57, 5, WEEKOFYEAR (SUBSTR(DATE,1,6)||'01')+5),
       FIXED (DECODE (LEAST (d+1, 36-c), d+1, NULL, 36-c), 2),
       FIXED (DECODE (LEAST (d+1, 37-c), d+1, NULL, 37-c), 2),
       FIXED (NULL, 2), FIXED (NULL, 2), FIXED (NULL, 2),
       FIXED (NULL, 2), FIXED (NULL, 2), ''
 FROM (SELECT DAYOFWEEK(SUBSTR(DATE,1,6)||'01')-1         c,
              DAY(SUBDATE(SUBSTR(CHR(YEAR(DATE)*100+(NUM(
              SUBSTR(DATE,5,2))MOD 12+1)),1,6)||'01',1))  d
         FROM dual)
  WHERE 36-c < d+1
ORDER BY 9!
FETCH INTO :A!
ROLLBACK WORK RELEASE!
