Pivot wide to long cannot find table name

Question

I am running the following cross apply sql query, however, the query cannot find the table.

For example:

SELECT sport,event_names
FROM table_name t
CROSS APPLY 
(
    VALUES
        (event_1),
        (event_2),
        (event_3)
) x (event_names);

903. 00000 - "invalid table name"

I can access this table when running the following query:

select * from table_name;

Answer 1

Table Value Constructor supported from 23c but you might want to use text literals to specify values.

SQL> create table table_name as select 0 sport;

Table created.

SQL> SELECT sport,event_names
  2  FROM table_name t
CROSS APPLY
(
    VALUES
        ('event_1'),
        ('event_2'),
        ('event_3')
) x (event_names);  3    4    5    6    7    8    9

     SPORT EVENT_N
---------- -------
         0 event_1
         0 event_2
         0 event_3

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

Answer 2

Oracle does not support table-row constructor syntax VALUES(). Your code looks like SQL Server.

In Oracle, you would typically need to select ... from dual instead of values() - so this unpivot query would become:

SELECT t.sport, x.event_names
FROM table_name t
CROSS APPLY (
    SELECT event_1 event_names FROM DUAL
    UNION ALL SELECT event_2 FROM DUAL
    UNION ALL SELECT event_3 FROM DUAL
) x