| index

View V_CURRENT_PARTY_ADDRESSES

Columns

Name | Type | Optional | Default | Comments
ID | NUMBER | Y |  |
ADDRESS_CODE | VARCHAR2(80) |  |  |
SUB_BUILDING | VARCHAR2(40) | Y |  |
BUILDING | VARCHAR2(40) | Y |  |
STREET | VARCHAR2(60) | Y |  |
CITY | VARCHAR2(40) |  |  |
POSTCODE | VARCHAR2(8) | Y |  |
START_DATE | DATE | Y |  |
END_DATE | DATE | Y |  |
COMMENTS | VARCHAR2(255) | Y |  |
RT_CODE | VARCHAR2(80) | Y |  |
MAX_START_DATE | DATE | Y |  |

SQL

    CREATE OR REPLACE VIEW V_CURRENT_PARTY_ADDRESSES AS
    SELECT "ID","ADDRESS_CODE","SUB_BUILDING","BUILDING","STREET","CITY","POSTCODE","START_DATE","END_DATE","COMMENTS","RT_CODE","MAX_START_DATE"
    FROM (SELECT parties.id,
           addresses.code AS address_code,
           addresses.sub_building,
           addresses.building,
           addresses.street,
           addresses.city,
           addresses.postcode,
           party_addresses.start_date,
           party_addresses.end_date,
           party_addresses.comments,
           party_address_roles.rt_code,
           MAX(party_addresses.start_date) over(PARTITION BY parties.ID, rt_code) AS max_start_date
      FROM addresses,
           party_addresses,
           party_address_roles,
           parties
     WHERE addresses.code = party_addresses.addr_code
           AND party_addresses.start_date = party_address_roles.paddr_start_date
           AND party_addresses.addr_code = party_address_roles.paddr_addr_code
           AND party_addresses.prty_id = party_address_roles.paddr_prty_id
           AND parties.id = party_addresses.prty_id)
     WHERE start_date = max_start_date
     ORDER BY ID;