|
SQL>
SQL> Create table objects ( oid int primary key, name varchar2(255) );
Table created.
SQL>
SQL> Create table attributes(
2 attrId int primary key, attrName varchar2(255),
3 datatype varchar2(25)
4 );
Table created.
SQL>
SQL> Create table object_Attributes(
2 oid int, attrId int, value varchar2(4000),
3 primary key(oid,attrId)
4 );
Table created.
SQL>
SQL> Create table Links (
2 oid1 int, oid2 int,
3 primary key (oid1, oid2)
4 );
Table created.
SQL>
SQL> insert into attributes values ( 1, 'DATE_OF_BIRTH', 'DATE' );
1 row created.
SQL> insert into attributes values ( 2, 'FIRST_NAME', 'STRING' );
1 row created.
SQL> insert into attributes values ( 3, 'LAST_NAME', 'STRING' );
1 row created.
SQL>
SQL> insert into objects values ( 1, 'PERSON' );
1 row created.
SQL> insert into object_Attributes values( 1, 1, '15-mar-1965' );
1 row created.
SQL> insert into object_Attributes values( 1, 2, 'Thomas' );
1 row created.
SQL> insert into object_Attributes values( 1, 3, 'Kyte' );
1 row created.
SQL>
SQL> insert into objects values ( 2, 'PERSON' );
1 row created.
SQL> insert into object_Attributes values( 2, 1, '21-oct-1968' );
1 row created.
SQL> insert into object_Attributes values( 2, 2, 'John' );
1 row created.
SQL> insert into object_Attributes values( 2, 3, 'Smith' );
1 row created.
SQL>
SQL> select * from (
2 select
3 max(decode(attrName, 'FIRST_NAME', value, null)) first_name,
4 max(decode(attrName, 'LAST_NAME', value, null)) last_name,
5 max(decode(attrName, 'DATE_OF_BIRTH', value, null)) date_of_birth
6 from objects, object_attributes, attributes
7 where attributes.attrName in ( 'FIRST_NAME', 'LAST_NAME', 'DATE_OF_BIRTH' )
8 and object_attributes.attrId = attributes.attrId
9 and object_attributes.oid = objects.oid
10 and objects.name = 'PERSON'
11 group by objects.oid
12 )
13 where last_name = 'Smith' or date_of_birth like '%-mar-%'
14 /
FIRST_NAME
--------------------------------------------------------------------------------
LAST_NAME
--------------------------------------------------------------------------------
DATE_OF_BIRTH
--------------------------------------------------------------------------------
Thomas
Kyte
15-mar-1965
John
Smith
21-oct-1968
FIRST_NAME
--------------------------------------------------------------------------------
LAST_NAME
--------------------------------------------------------------------------------
DATE_OF_BIRTH
--------------------------------------------------------------------------------
SQL>
SQL> drop table objects;
Table dropped.
SQL> drop table attributes;
Table dropped.
SQL> drop table object_attributes;
Table dropped.
SQL> drop table links;
Table dropped.
SQL>
|