WITH RECURSIVE и ORDER BY

Mon, 26 Nov 2007 23:49:48 -0800 

Таблица:

CREATE TABLE BE
(
  DB BIGINT,
  RN BIGINT,

  HDB BIGINT DEFAULT NULL,
  HRN BIGINT DEFAULT NULL,

  CODE VARCHAR(20),

  NAME VARCHAR(254),
  CONSTRAINT PK_BE PRIMARY KEY (RN, DB),
  CONSTRAINT NB_BE_CODE CHECK (CODE IS NOT NULL),
  CONSTRAINT NB_BE_NAME CHECK (NAME IS NOT NULL),
  CONSTRAINT UQ_BE_CODE UNIQUE (CODE)
);

Запрос:
WITH RECURSIVE
  B AS
  (
    SELECT
      B_H.DB,
      B_H.RN,
      B_H.HDB,
      B_H.HRN,
      B_H.CODE,
      B_H.NAME,
      0 AS LVL
    FROM BE B_H
    WHERE B_H.HDB IS NULL AND B_H.HRN IS NULL
    ORDER BY B_H.NAME

    UNION ALL

    SELECT
      B_L.DB,
      B_L.RN,
      B_L.HDB,
      B_L.HRN,
      B_L.CODE,
      B_L.NAME,
      B.LVL + 1 AS LVL
    FROM BE B_L
      JOIN B ON B_L.HDB = B.DB AND B_L.HRN = B.RN
    ORDER BY B_L.NAME
  )
SELECT
  B.DB,
  B.RN,
  B.HDB,
  B.HRN,
  B.CODE,
  IIF(B.LVL > 0, LPAD(B.NAME, CHAR_LENGTH(B.NAME) + 3 * B.LVL, ' '),
B.NAME) AS NAME
FROM
  B

Выполнение запроса приводит к ошибке:
Can't format message 13:896 -- message system code -4.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 16, column 5.
UNION.

Версия сервера:
Server Version        = WI-T2.1.0.17092 Firebird 2.1 Beta 2
Server Implementation = Firebird/x86/Windows NT
Service Version       = 2

Вопрос: ошибка бага или необходимое ограничение на CTE

Ответить