iBase.ru Forum

Код: Выделить всё

CREATE TABLE CSTMONTH( // месяцы года
  MONTH_NO MONTHNO NOT NULL,
  CSTMONTH VARCHAR(8)
);

CREATE TABLE DEPT( // отделы
  DEPT_NO    DEPTNO NOT NULL,
  DEPT VARCHAR(25)
);

CREATE TABLE PDN( // расходы по отделам за каждый месяц
  PDN         DECIMAL(9,2) DEFAULT 0,
  FK_DEPT_NO  INTEGER,
  FK_MONTH_NO INTEGER
);

CREATE TABLE DEAL( // списки договоров по отделам за каждый месяц
  DEAL VARCHAR(25)
  VALUE         DECIMAL(9,2) DEFAULT 0,
  FK_DEPT_NO  INTEGER,
  FK_MONTH_NO INTEGER
);

Код: Выделить всё

SELECT
  D.DEPT_NO,
  C.MONTH_NO,
  D.DEPT AS DEPARTMENT,
  C.CSTMONTH,
  COALESCE(PDN.PDN, 0) AS PDN,
  COALESCE(DEAL.VALUE, 0) AS SUM_DOGOV
FROM CSTMONTH C
CROSS JOIN DEPT D
LEFT JOIN
  (SELECT
    SUM(PDN) AS PDN,
    FK_DEPT_NO,
    FK_MONTH_NO
  FROM PDN
  GROUP BY FK_DEPT_NO, FK_MONTH_NO
  ) AS PDN
  ON PDN.FK_DEPT_NO = D.DEPT_NO AND PDN.FK_MONTH_NO = C.MONTH_NO
LEFT JOIN
  (SELECT
    SUM(VALUE) AS VALUE,
    FK_DEPT_NO,
    FK_MONTH_NO
  FROM DEAL
  GROUP BY FK_DEPT_NO, FK_MONTH_NO
  ) AS DEAL
  ON DEAL.FK_DEPT_NO = D.DEPT_NO AND DEAL.FK_MONTH_NO = C.MONTH_NO

Код: Выделить всё

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 9, char 5.
CROSS.

Код: Выделить всё

SELECT
  C.MONTH_NO,
  D.DEPT_NO,
  D.DEPARTMENT,
  P.PDN,
  P.FK_DEPT_NO, 
  P.FK_MONTH_NO,
  COALESCE(S.SUMMA,0) AS SUM_DOGOVOR
FROM DEPARTMENT D
  INNER JOIN CSTMONTH C
    INNER JOIN 
      PDN P
        LEFT JOIN
              (SELECT
                D.FK_MONTH_NO,
                D.FK_DEPT_NO,
                Sum(D.DEALSUM) AS SUMMA,
                COUNT(*) AS CNT
              FROM DEAL AS D
              GROUP BY D.FK_MONTH_NO, D.FK_DEPT_NO) S
        ON P.FK_DEPT_NO = S.FK_DEPT_NO
            AND P.FK_MONTH_NO = S.FK_MONTH_NO
    ON C.MONTH_NO = P.FK_MONTH_NO
  ON D.DEPT_NO = P.FK_DEPT_NO
WHERE
  C.MONTH_NO  = :MONZ

Код: Выделить всё

SELECT P.FK_MONTH_NO, D.DEPT_NO, D.DEPARTMENT, P.PDN,
       (SELECT COALESCE(SUM(DEALSUM), 0)
          FROM DEAL
          WHERE FK_DEPT_NO = P.FK_DEPT_NO AND FK_MONTH_NO = P.FK_MONTH_NO
       ) AS SUM_DOGOVOR
  FROM DEPARTMENT D
       JOIN PDN P ON D.DEPT_NO = P.FK_DEPT_NO
  WHERE P.MONTH_NO  = :MONZ