Как передать результат запроса функции, являющейся частью того же самого?

Я не думаю, что есть способ сделать то, что вы хотите; Я уверен, что нет простого способа сделать что-то подобное.

Настраивать

Рассмотрим базу данных с двумя таблицами: таблица (химических) элементов — периодическая таблица — и таблица (США) состояний.

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL PRIMARY KEY
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE,
    name            CHAR(20) NOT NULL UNIQUE,
    atomic_weight   DECIMAL(8, 4) NOT NULL,
    pt_period       SMALLINT NOT NULL
                    CHECK (pt_period BETWEEN 1 AND 7),
    pt_group        CHAR(2) NOT NULL
                    -- 'L' for Lanthanoids, 'A' for Actinoids
                    CHECK (pt_group IN ('1', '2', 'L', 'A', '3', '4', '5', '6',
                                        '7', '8', '9', '10', '11', '12', '13',
                                        '14', '15', '16', '17', '18')),
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

CREATE TABLE US_States
(
    code    CHAR(2) NOT NULL PRIMARY KEY,
    name    VARCHAR(15) NOT NULL UNIQUE
);

Я предполагаю, что вы можете заполнить две таблицы правильными данными (см. веб-элементы для периодической таблицы; демонстрационная база данных Informix «сохраняет» таблицу state, изоморфную используемой здесь таблице US_States).

Теперь рассмотрим процедуру states_starting():

CREATE FUNCTION states_starting(initial CHAR(1)) RETURNING VARCHAR(15);

    DEFINE result VARCHAR(15);

    FOREACH SELECT Name
              INTO result
              FROM US_States
              WHERE Code[1] = initial
              ORDER BY Name
        RETURN result WITH RESUME;
     END FOREACH;

END FUNCTION;

Адаптация запросов в вопросе

Я был немного удивлен, что нотация vt(result) работает — но она работает, обозначая псевдоним таблицы vt и имя столбца result. Следовательно, адаптация сработавшего запроса:

SELECT *
  FROM Elements, TABLE(states_starting('M')) vt(result)

Это генерирует декартово произведение 118 элементов и 8 состояний с именами, начинающимися с «M», всего для 944 строк. Чуть более разумный запрос:

SELECT *
  FROM Elements JOIN TABLE(states_starting('M')) AS vt(result)
    ON Elements.Symbol[1] = vt.result[1]
 ORDER BY Elements.Atomic_Number

Это генерирует результаты для 6 элементов (магний, марганец, мейтнерий, менделевий, молибден, московий: ртуть не фигурирует, потому что его символ — Hg, который не начинается с буквы М) и 8 состояний, как и раньше, для 48 строк.

Адаптация запроса, который вы хотите выполнить, выглядит примерно так:

SELECT *
  FROM Elements AS e
  JOIN TABLE(states_starting(e.name[1])) AS vt(result)
    ON Elements.Symbol[1] = vt.result[1]
 ORDER BY Elements.Atomic_Number

Однако это не работает, выдавая ошибку:

-217: Column (name) not found in any table in the query (or SLV is undefined).

Это не идентично ошибке в вопросе; У меня не получилось это воспроизвести. Но это симптом проблем, с которыми сталкивается этот запрос.

Проблема в том, что в TABLE(…) имя e неизвестно, но удаление e. из аргумента ничего не меняет.

Кроме того, чтобы сгенерировать полностью правильный результат, выражение TABLE(…) должно быть оценено несколько раз, по одному разу для каждой отдельной начальной буквы. Итак, вам понадобится набор результатов таблицы от нескольких вызовов функции в выражении TABLE(…) с разными аргументами. Но это не то, как таблицы работают в SQL. Они должны быть «фиксированными». Постоянный аргумент функции дает один результирующий набор и выглядит как таблица. Но попытка вызвать его несколько раз с разными аргументами и работа с результирующим набором будет (в лучшем случае) сложной задачей — это не то, как работает SQL.

Я не совсем удовлетворен объяснением. Идея, которую я пытаюсь выразить, почти наверняка является причиной того, что запросы не работают, но я не доволен тем, что объяснил ее хорошо.

Я пробовал множество вариантов. Например, если вы создаете GROUP_CONCAT агрегат, вы можете попробовать:

SELECT group_concat(states_starting(a))
  FROM (SELECT DISTINCT NAME[1] AS a FROM us_states)
 GROUP BY a

но это генерирует:

-686: Function (states_starting) has returned more than one row.

Я не думаю, что создание SET или LIST из результата функции помогает.

Тестирование в Mac OS X 10.11.5 с Informix 12.10.FC6 (и ClientSDK 4.10.FC6, а также SQLCMD 90.01 — не имеет отношения к одноименному johnny-come-lately от Microsoft).