Hi,
Is it possible to join two subqueries on a multiset collection?
E.g. the following would return 1 row, because the multisets in both subqueries are identical.
with a as
(
SELECT
1 as ID,
CAST
(
MULTISET
(
SELECT 'A' from dual
union all select 'B' from dual
union all select 'C' from dual
)
AS sys.odcivarchar2list
)
AS MyList
FROM dual
where rownum = 1
),
b as
(
SELECT
1 as ID,
CAST
(
MULTISET
(
SELECT 'A' from dual
union all select 'B' from dual
union all select 'C' from dual
)
AS sys.odcivarchar2list
)
AS MyList
FROM dual
where rownum = 1
)
select * from a inner join b on a.mylist = b.mylist
;
When I run this SQL I receive the following error
ORA-00932: inconsistent datatypes: expected - got SYS.ODCIVARCHAR2LIST
00932. 00000 - "inconsistent datatypes: expected %s got %s"
Thanks