Selecting different subtype values in one query

I'm new with oracle and handling subtypes. I need to select an album that is of MP3_TYPE and both values of MEDIA_TYPE in DISK_TYPE: Vinyl, Audio CD.

Here is the create subtype query:

create or replace type disk_type under album_type 
( mediaType         varchar(10),
  diskNum           number(2), -- number of disks
  diskUsedPrice     number(9,2),
  diskDeliveryCost  number(9,2), 
  overriding member function discountPrice return number)
/
create or replace type mp3_type under album_type
(downloadSize   number, -- size in MB
overriding member function discountPrice return number)
/

I can select all 3 types individually but it doesn't seem to be working when all 3 are in the one query. Not great with treat functions so assuming it is some syntax issue.

Here is what I've done:

select
    t1.albumtitle
from albums t1
where value(t1) IS OF (mp3_type) 
    and treat(value(t1) as disk_type).mediatype = 'Vinyl'
    and treat(value(t1) as disk_type).mediatype = 'Audio CD';

Any ideas?

Solution

In the code you've shown so far, mp3_type and disk_type are both subtype of album_type. Which seems reasonable. But that means a single object in the table cannot be both subtypes at once; so it can't have the attributes of both subtypes.

If a row is mp3_type then treating it as disk_type doesn't cause the disk_type attributes to be set. When you treat that object as disk_type those attributes will be - must be - null.

insert into albums values (album_type('ABC'));
insert into albums values (mp3_type('BCD', 123));
insert into albums values (disk_type('DEF', 'Vinyl', 1, 12.34, 1.23));

select
    t1.albumtitle
from albums t1
where value(t1) IS OF (mp3_type) 
    and treat(value(t1) as disk_type).mediatype = 'Vinyl'
    and treat(value(t1) as disk_type).mediatype = 'Audio CD';

no rows selected

select
    t1.albumtitle,
    treat(value(t1) as disk_type).mediatype
from albums t1
where value(t1) IS OF (mp3_type);

ALBUMTITLE                     TREAT(VALU
------------------------------ ----------
BCD

I need to select an album that is of MP3_TYPE and both values of MEDIA_TYPE in DISK_TYPE: Vinyl, Audio CD

If it is MP3_TYPE then MEDIA type is null; but even with a disk_type entry the media type cannot be both Vinyl and CD at the same time.

Perhaps you just meant or use or instead of and:

select
    t1.albumtitle,
    treat(value(t1) as mp3_type).downloadsize as downloadsize,
    treat(value(t1) as disk_type).mediatype as mediatype
from albums t1
where
    value(t1) IS OF (mp3_type) 
    or (
        value(t1) IS OF (disk_type)
        and (
            treat(value(t1) as disk_type).mediatype = 'Vinyl'
            or treat(value(t1) as disk_type).mediatype = 'Audio CD'
        )
    );

ALBUMTITLE                     DOWNLOADSIZE MEDIATYPE 
------------------------------ ------------ ----------
BCD                                     123           
DEF                                         Vinyl