SLK-581 Linkage in SQL

query.sql

set sqlformat ansiconsole
--See: http://www.aihw.gov.au/WorkArea/DownloadAsset.aspx?id=60129551915
--http://meteor.aihw.gov.au/content/index.phtml/itemId/349510
with raw_data as (
    select
        upper('Smith') last,
        upper('Nicholas') first,
        to_date('29-01-1959', 'dd-mm-yyyy') dob,
        'M' gender
    from dual
), linkage_fields as (
select
    last,
    first,
    to_char(dob, 'DD') dob_day,
    to_char(dob, 'MM') dob_month,
    to_char(dob, 'YYYY') dob_year,
    gender,
    substr(last, 2, 1) fn2,
    substr(last, 3, 1) fn3,
    substr(last, 5, 1) fn5,
    substr(first, 2, 1) gn2,
    substr(first, 3, 1) gn3
from raw_data
)
select fn2||fn3||fn5||gn2||gn3||dob_day||dob_month||dob_year||gender slk
from linkage_fields;