I have created a VARRAY of a type and i'm trying to fire SQL queries on them.
This is what I have doneNow, I have inserted some values in this table asCode:CREATE TYPE blog_post AS OBJECT ( blogname varchar(20) ); / CREATE TYPE blog_posts_arr AS VARRAY(20) OF blog_post; / CREATE TABLE topic ( topic_id VARCHAR(20), name VARCHAR(20), time TIMESTAMP, datetop DATE, blog_posts blog_posts_arr, PRIMARY KEY (topic_id) );I am now trying to fire an SQL query on this table which will return me the topic name and the number of blog_posts associated with the topic in the decreasing order of the blog posts.Code:INSERT INTO topic VALUES( 't3', 'football', NULL, NULL, blog_posts_arr( blog_post('p25') )); INSERT INTO topic VALUES( 't4', 'golf', NULL, NULL, blog_posts_arr( blog_post('p24'), blog_post('p8') )); INSERT INTO topic VALUES( 't5', 'tennis', NULL, NULL, blog_posts_arr( blog_post('p1'), blog_post('p18'), blog_post('p19'), blog_post('p20'), blog_post('p21') ));
Can someone please help me write this query?
I tried getting the count of blogs in a topic using
But this doesnt work. How should my sql look like?Code:select count(t.blog_posts) from topic t, table(t.blog_posts);




Reply With Quote