max and decode function : MAX « Aggregate Functions « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Aggregate Functions
3. MAX

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>