How to use a materialized subquery inside where/in statement?

Question

Having the following statement:

WITH tokenkeys 
as
(
    select regexp_substr('A set of words from other side','[^ ]+', 1, level) WORDSPLIT from dual
    connect by regexp_substr('A set of words from other side', '[^ ]+', 1, level) is not null
)
select * from tokenkeys

It output a table like:

|WORDSPLIT|
|A        |
|set      |
|of       |
|words    |
|from     |
|other    |
|side     |

I want to use this temporary collection inside a where/in statement:

WITH tokenkeys 
as
(
    select regexp_substr('A set of words from other side','[^ ]+', 1, level) WORDSPLIT from dual
    connect by regexp_substr('A set of words from other side', '[^ ]+', 1, level) is not null
)
select p.* from people p
where 
p.name in (tokenkeys.wordsplit)
or UTL_MATCH.EDIT_DISTANCE_SIMILARITY(p.lastname, tokenkeys.wordsplit) > 60 ???
or ....

I have two questions:

1. How to do p.name in (tokenkeys.wordsplit)?
2. How to mix the temporary collection with an edit_distance_similarity function, and get the max result:

Example:

UTL_MATCH.EDIT_DISTANCE_SIMILARITY(p.lastname, tokenkeys.wordsplit) > 60
                                    //I want the greater

max(UTL_MATCH.EDIT_DISTANCE_SIMILARITY(p.lastname, tokenkeys.wordsplit))
                                    //I can't yet test this line.

Many thanks.

Answer 1

use join with distinct (transfer the where clause to the join condition-on):

WITH tokenkeys 
as
(
    select regexp_substr('A set of words from other side','[^ ]+', 1, level) WORDSPLIT from dual
    connect by regexp_substr('A set of words from other side', '[^ ]+', 1, level) is not null
)
select distinct p.* from people p
join tokenkeys on p.name in (tokenkeys.wordsplit)
or UTL_MATCH.EDIT_DISTANCE_SIMILARITY(p.lastname, tokenkeys.wordsplit) > 60