Как создать таблицу с ручными данными на лету

Question

Допустим, я хочу определить некоторые табличные данные на лету для использования в запросе, не создавая физическую таблицу:

      +------------+------------+
|COLUMN_VALUE|COLUMN_VALUE|
+------------+------------+
|1           |a           |
|2           |b           |
|5           |e           |
|4           |d           |
|3           |c           |
+------------+------------+

(обратите внимание на порядок)

Как сделать это максимально лаконично и просто?

Что я мог придумать (не очень красиво):

      with
    x as (
        select
            column_value
        from
            table (sys.odcinumberlist(1, 2, 5, 4, 3))),

    y as (
        select
            column_value
        from
            table (sys.odcivarchar2list('a', 'b', 'e', 'd', 'c')))
select
    x.column_value,
    y.column_value
from
    x
inner join y on x.rownum = y.rownum;

Однако это не работает, так как выдает ошибку с ORA-01747: invalid user.table.column, table.column, or column specification. По-видимому, rownumпсевдостолбец не поддерживается при использовании встроенного table()функция.

С использованием row_number() over (order by column_value asc)в CTE, похоже, работает, но принудительно упорядочивает значения столбцов. Это нежелательно, так как значения должны появляться в том порядке, в котором они определены в табличной функции.

Answer 1

На форуме Oracle Database Ideas есть предложение сообщества добавить ordinalityпсевдостолбец в скалярные коллекции, но я бы сказал, что это вряд ли будет реализовано при нашей жизни. Существует также предложение по синтаксису, позволяющему объявлять наборы на лету с помощью valuesпункт, похожий на списки Postgres Values, хотя я не могу найти его прямо сейчас.

В настоящее время ваши варианты:

1. Используйте несколько запросов с union allдля явного определения всех необходимых строк.

2. Определите настраиваемые типы объектов и коллекций, чтобы использовать декларативный синтаксис объектов.

Я согласен, что ни то, ни другое не идеально.

Пример select from dualподход:

      with test_data (id, somevalue) as
     (
       select 1, 'a' from dual union all
       select 2, 'b' from dual union all
       select 5, 'e' from dual union all
       select 4, 'd' from dual union all
       select 3, 'c' from dual
     )
select id, somevalue
from   test_data

Что касается сгенерированной нумерации строк, вы можете использовать

      row_number() over(order by null)

Технически это не гарантирует сохранения порядка, но на практике, похоже, это происходит в Oracle 21c.

      select column_value
     , row_number() over (order by null) as rn
from   table (sys.odcivarchar2list('a', 'b', 'e', 'd', 'c'));

COLUMN_VALUE         RN
------------ ----------
a                     1
b                     2
e                     3
d                     4
c                     5

Что касается вашей ошибки ORA-01747 для , это не из-за каких-либо ограничений вокруг table()оператор. Есть две проблемы с такой конструкцией (упрощенная версия вашего запроса):

      with demo as
     ( select dummy from dual )
select x.rownum from demo x

1. КТР demoне имеет столбца с именем rownum. У него только один столбец ( dummy), так что это единственный x.column, на который вы можете ссылаться.
2. Даже если вы добавите rownumк запросу, вы не можете ссылаться на него как на столбец, например x.rownumпотому что это ключевое слово. Решением было бы использовать псевдоним в CTE как что-то вроде «seq» или «rn», что затем можно использовать для ссылки:

      with demo as
     ( select dummy, rownum as rn from dual )
select x.rn from demo x

Answer 2

Решение состояло в том, чтобы использовать псевдоним для столбца rownum (также убрал запрос):

      select
    x.column_value,
    y.column_value
from
    (select column_value, rownum as rn from table (sys.odcinumberlist(1, 2, 5, 4, 3))) x
        inner join
        (select column_value, rownum as rn from table (sys.odcivarchar2list('a', 'b', 'e', 'd', 'c'))) y on x.rn = y.rn;