Oracle: Combine arbitrary list with select from a table

Question

Let's just say I have a USERS table with four users in it:

+----+-------+
| id | login |
+----+-------+
| 1  | guest |
| 2  | admin |
| 3  | alice |
| 4  | bob   |
+----+-------+

I want to combine it with an arbitrary list of attributes. Say my list is: 'alice', 'bob', 'charles', 'dan'. I'd like to write a select which looks like this (to then insert into another table).

+----+-------+-------------+
| id | login | friend_name |
+----+-------+-------------+
| 1  | guest | alice       |
| 2  | admin | bob         |
| 3  | alice | charles     |
| 4  | bob   | dan         |
+----+-------+-------------+

Current attempt:

SELECT u.id,
  u.name,
  vals.column_value
FROM users u
INNER JOIN TABLE(sys.odcivarchar2list('alice', 'bob', 'charles', 'dan')) vals
ON vals.column_value IS NOT NULL ;

Only, this will create a Cartesian product. The result will include four rows for each user:

+----+-------+-------------+
| id | login | friend_name |
+----+-------+-------------+
| 1  | guest | alice       |
| 1  | guest | bob         |
| 1  | guest | charles     |
| 1  | guest | dan         |
| 2  | admin | alice       |
| 2  | admin | bob         |
| 2  | admin | charles     |
| 2  | admin | dan         |
| 3  | alice | alice       |
| 3  | alice | bob         |
| 3  | alice | charles     |
| 3  | alice | dan         |
| 4  | bob   | alice       |
| 4  | bob   | bob         |
| 4  | bob   | charles     |
| 4  | bob   | dan         |
+----+-------+-------------+

This one doesn't work but it's what I'd like to do:

SELECT u.id,
  u.name,
  vals.column_value
FROM users u
INNER JOIN TABLE(sys.odcivarchar2list('alice', 'bob', 'charles', 'dan')) vals
ON vals.rownum = u.rownum ;

Of course, it doesn't work because, the two tables do not have a rownum attribute.

How can I write a select which inserts a list of values into a column on a select without multiplying these out?

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-08-28T10:35:40

There may be other ways of doing this, but one method is:

SELECT u.id, u.name, vals.column_value
FROM (SELECT u.*, rownum as seqnum
      FROM users u
     ) u JOIN
     (SELECT vals.*, rownum as seqnum
      FROM TABLE(sys.odcivarchar2list('alice', 'bob', 'charles', 'dan')) vals
     ) vals
     ON vals.rn = u.rn ;

Oracle: Combine arbitrary list with select from a table

1 Answers