Writing an SQL statement that references a nested VARRAY

Question

I am trying to write an SQL statement that queries a column actors (defined as VARRAY(5)) in a table named movie without using PL/SQL, to answer the question:

List any actors that appear in multiple movies, showing the movie title and actor

select a.column_value, count(*), m.title
from movie m, table (m.actors) a
where count(*) > 2,
group by a.column_value;

Please **[edit]** your question and add the `create table` statement for the table `movie`, some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). ([edit] your question by clicking on the [edit] link below the question. Do not post code or additional information in comments) — , Oct 02 '18 at 06:13

score 2 · Accepted Answer · answered Oct 02 '18 at 06:37

You should use the HAVING clause for checking the condition instead of where clause and additionally LISTAGG may be used to show the movie titles.

CREATE OR REPLACE TYPE actortype IS VARRAY(5) OF VARCHAR2(20);
/

create table movie ( id integer , title VARCHAR2(100), actors actortype );

INSERT INTO movie (
     id,
     title,
     actors
) VALUES (
     1,
     'The Shawshank Redemption',
     actortype('Morgan Freeman','Tim Robbins','Bob Gunton')
);
INSERT INTO movie (
     id,
     title,
     actors
) VALUES (
     1,
     'The Dark Knight',
     actortype('Morgan Freeman','Christian Bale','Heath Ledger')
);

Query

SELECT a.column_value as Actor,
       COUNT(*) as num_of_movies,
       LISTAGG( m.title, ',') WITHIN GROUP ( ORDER BY id ) as movies
FROM movie m,
     TABLE ( m.actors ) a
GROUP BY a.column_value
HAVING COUNT(*) > 1;

ACTOR          NUM_OF_MOVIES MOVIES
------------  -------------  -------
Morgan Freeman  2            The Dark Knight,The Shawshank Redemption

Writing an SQL statement that references a nested VARRAY

1 Answers1