3. Samples

3.1. Create

-- CREATE TABLE CFE.INTEREST_PERIOD
CREATE TABLE cfe.interest_period (
    id_instrument                VARCHAR (40)    NOT NULL
    , id_fixingmode              NUMBER (5)      DEFAULT 0 NOT NULL
    , fixing_date                DATE
    , change_date                DATE
    , base_rate                  VARCHAR (12)
    , base_margin_rate           DECIMAL (12,9)
    , par_rate                   VARCHAR (12)
    , par_margin_rate            DECIMAL (12,9)
    , id_payment_convention      VARCHAR (12)
    , id_day_count_convention    VARCHAR (12)
    , id_day_incl_convention     VARCHAR (12)
    , fix_amount                 DECIMAL (23,5)
    , id_currency_fix_amount     VARCHAR (3)
    , id_script                  VARCHAR (12)
)
;

-- SIMPLE
CREATE TABLE employees (
    employee_number      INT         NOT NULL
    , employee_name      CHAR (50)   NOT NULL
    , department_id      INT
    , salary             INT
    , PRIMARY KEY ( employee_number )
    , UNIQUE ( employee_name )
    , FOREIGN KEY ( department_id )
        REFERENCES departments ( department_id )
) PARALLEL COMPRESS NOLOGGING
;

-- COMPLEX
CREATE TABLE employees (
    employee_number      INT         NOT NULL
    , employee_name      CHAR (50)   NOT NULL
    , department_id      INT
    , salary             INT
    , CONSTRAINT employees_pk
        PRIMARY KEY ( employee_number )
    , CONSTRAINT fk_departments
        FOREIGN KEY ( department_id )
        REFERENCES departments ( department_id )
) PARALLEL COMPRESS NOLOGGING
;

-- COMPLEX WITH MANY REFERENCES
CREATE TABLE employees (
    employee_number      INT         NOT NULL
    , employee_name      CHAR (50)   NOT NULL
    , department_id      INT
    , salary             INT
    , CONSTRAINT employees_pk
        PRIMARY KEY (   employee_number
                        , employee_name
                        , department_id )
    , CONSTRAINT fk_departments
        FOREIGN KEY (   employee_number
                        , employee_name
                        , department_id )
        REFERENCES departments (    employee_number
                                    , employee_name
                                    , department_id )
) PARALLEL COMPRESS NOLOGGING
;

-- CREATE TABLE CFE.RECONCILIATION_NOMINAL_HST 2
CREATE TABLE cfe.reconciliation_nominal_hst PARALLEL COMPRESS NOLOGGING
    AS ( SELECT /*+ PARALLEL */
            (   SELECT id_execution_ref
                FROM cfe.execution_ref c
                    INNER JOIN cfe.execution_v d
                        ON c.value_date = d.value_date
                            AND c.posting_date = d.posting_date
                            AND d.flag = 'L' ) id_execution_ref
            , b.id_instrument_ref
            , a.value_date
            , a.nominal_balance
        FROM cfe.reconciliation_nominal a
            INNER JOIN cfe.instrument_ref b
                ON a.id_instrument = b.id_instrument )
;

-- Z COMPLEX WITH MANY REFERENCES
-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
create table EMPLOYEES (
  EMPLOYEE_NUMBER    int       not null,
  EMPLOYEE_NAME      char (50) not null,
  DEPARTMENT_ID      int,
  SALARY             int,
  constraint EMPLOYEES_PK
    primary key ( EMPLOYEE_NUMBER,
                  EMPLOYEE_NAME,
                  DEPARTMENT_ID ),
  constraint FK_DEPARTMENTS
    foreign key ( EMPLOYEE_NUMBER,
                  EMPLOYEE_NAME,
                  DEPARTMENT_ID )
    references DEPARTMENTS (  EMPLOYEE_NUMBER,
                              EMPLOYEE_NAME,
                              DEPARTMENT_ID )
) parallel compress nologging
;

-- SIMPLE
CREATE INDEX cfe.version_info_idx1
    ON cfe.version_info(    major_version
                            , minor_version
                            , patch_level )
;

-- UNIQUE
CREATE UNIQUE INDEX cfe.interest_period_idx1
    ON cfe.interest_period( id_instrument, change_date )
;

-- MANY COLUMNS
CREATE UNIQUE INDEX cfe.version_info_idx2
    ON cfe.version_info(    major_version
                            , minor_version
                            , patch_level
                            , major_version
                            , minor_version
                            , patch_level )
;

-- MANY COLUMNS WITH TAIL OPTIONS
CREATE UNIQUE INDEX cfe.version_info_idx2
    ON cfe.version_info(    major_version
                            , minor_version
                            , patch_level
                            , major_version
                            , minor_version
                            , patch_level ) PARALLEL COMPRESS NOLOGGING
;


-- Z MANY COLUMNS WITH TAIL OPTIONS
-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
create unique index CFE.VERSION_INFO_IDX2
  on CFE.VERSION_INFO(  MAJOR_VERSION,
                        MINOR_VERSION,
                        PATCH_LEVEL,
                        MAJOR_VERSION,
                        MINOR_VERSION,
                        PATCH_LEVEL ) parallel compress nologging
;

3.2. Alter

-- ALTER TABLE ADD FOREIGN KEY
ALTER TABLE cfe.ledger_acc_entry_manual
    ADD FOREIGN KEY (id_manual_posting_batch)
        REFERENCES manual_posting_batch (id_manual_posting_batch)
;

-- ALTER TABLE DROP COLUMN
ALTER TABLE risk.collateral
    DROP COLUMN id_status
;

-- ALTER TABLE ADD COLUMN
ALTER TABLE risk.collateral
    ADD COLUMN id_status VARCHAR (1) NULL
;

-- ALTER ALTER COLUMN DROP COLUMN
ALTER TABLE risk.collateral
    ALTER COLUMN id_status VARCHAR (1) NOT NULL
;

-- ORACLE ALTER TABLE ADD COLUMN
ALTER TABLE risk.collateral
    ADD id_status VARCHAR (1) NULL
;

-- ORACLE ALTER TABLE MODIFY
ALTER TABLE risk.collateral
    MODIFY id_status VARCHAR (1) NOT NULL
;

-- ORACLE ADD MULTIPLE COLUMNS
ALTER TABLE customers
    ADD (   customer_name    VARCHAR2 (45)
            , city           VARCHAR2 (40)   DEFAULT 'SEATTLE')
;

-- ORACLE MODIFY MULTIPLE COLUMNS
ALTER TABLE customers
    MODIFY (    customer_name    VARCHAR2 (100)  NOT NULL
                , city           VARCHAR2 (75)   DEFAULT 'SEATTLE' NOT NULL)
;

-- RENAME
ALTER TABLE departments
    RENAME COLUMN department_name TO dept_name
;

3.3. Select

-- INSERT NEW LEDGER ACCOUNTS
SELECT /*+ PARALLEL */
    cfe.id_account_seq.nextval
    , "a"."code"
    , a."id_currency"
    , a.id_fee_type
    , current_date
    , NULL
    , id_accounting_scope_code
FROM (  SELECT *
        FROM (  SELECT DISTINCT
                    c.code code
                    , d.id_currency
                    , NULL id_fee_type
                    , c1.id_accounting_scope_code
                FROM cfe.ledger_branch c
                    INNER JOIN cfe.accounting_scope c1
                        ON c1.id_accounting_scope = c.id_accounting_scope
                            AND c1.id_status = 'C'
                    , common.ledger_currency d
                MINUS
                SELECT DISTINCT
                    c.code
                    , d.id_currency
                    , NULL id_fee_type
                    , c.id_accounting_scope_code
                FROM cfe.ledger_account c
                    INNER JOIN common.ledger_currency d
                        ON c.id_currency = d.id_currency )
        UNION
        SELECT *
        FROM (  SELECT DISTINCT
                    c.code
                    , d.id_currency
                    , NULL id_fee_type
                    , c1.id_accounting_scope_code
                FROM cfe.ledger_branch c
                    INNER JOIN cfe.accounting_scope c1
                        ON c1.id_accounting_scope = c.id_accounting_scope
                            AND c1.id_status = 'C'
                    , common.ledger_currency d
                MINUS
                SELECT DISTINCT
                    c.code
                    , d.id_currency
                    , NULL id_fee_type
                    , c.id_accounting_scope_code
                FROM cfe.ledger_account c
                    INNER JOIN common.ledger_currency d
                        ON c.id_currency = d.id_currency )
        UNION
        SELECT *
        FROM (  SELECT DISTINCT
                    c.code code
                    , d.id_currency
                    , e.id_fee_type
                    , c1.id_accounting_scope_code
                FROM cfe.ledger_branch c
                    INNER JOIN cfe.accounting_scope c1
                        ON c1.id_accounting_scope = c.id_accounting_scope
                            AND c1.id_status = 'C'
                    , common.ledger_currency d
                    , cfe.fee_type e
                MINUS
                SELECT DISTINCT
                    c.code
                    , d.id_currency
                    , e.id_fee_type
                    , c.id_accounting_scope_code
                FROM cfe.ledger_account c
                    INNER JOIN common.ledger_currency d
                        ON c.id_currency = d.id_currency
                    INNER JOIN cfe.fee_type e
                        ON c.id_fee_type = e.id_fee_type ) ) a
;

-- INSERT INTO LEDGER BRANCH BALANCE
WITH scope AS (
        SELECT *
        FROM cfe.accounting_scope
        WHERE id_status = 'C'
            AND id_accounting_scope_code = :SCOPE )
    , ex AS (
        SELECT *
        FROM cfe.execution
        WHERE id_status = 'R'
            AND value_date = (  SELECT Max( value_date )
                                FROM cfe.execution
                                WHERE id_status = 'R'
                                    AND ( :VALUE_DATE IS NULL
                                            OR value_date <= :VALUE_DATE ) ) )
    , fxr AS (
        SELECT  id_currency_from
                , fxrate
        FROM common.fxrate_hst f
            INNER JOIN ex
                ON f.value_date <= ex.value_date
        WHERE f.value_date = (  SELECT Max( value_date )
                                FROM common.fxrate_hst
                                WHERE id_currency_from = f.id_currency_from
                                    AND id_currency_into = f.id_currency_into
                                    AND value_date <= ex.value_date )
            AND id_currency_into = :BOOK_CURRENCY
        UNION ALL
        SELECT  :BOOK_CURRENCY
                , 1
        FROM dual )
SELECT /*+ PARALLEL */
    scope.id_accounting_scope
    , ex.value_date
    , ex.posting_date
    , a.gl_level
    , a.code
    , b.description
    , c.balance_bc
FROM ex
    , scope
    INNER JOIN cfe.ledger_branch_branch a
        ON a.id_accounting_scope = scope.id_accounting_scope
            AND a.code = a.code_inferior
    INNER JOIN cfe.ledger_branch b
        ON b.id_accounting_scope = scope.id_accounting_scope
            AND b.code = a.code
    INNER JOIN (    SELECT  b.code
                            , Round( d.amount * fxr.fxrate, 2 ) balance_bc
                    FROM scope
                        INNER JOIN cfe.ledger_branch_branch b
                            ON b.id_accounting_scope = scope.id_accounting_scope
                        INNER JOIN cfe.ledger_account c
                            ON b.code_inferior = c.code
                                AND c.id_accounting_scope_code = scope.id_accounting_scope_code
                        INNER JOIN (    SELECT  id_account
                                                , Sum( amount ) balance
                                        FROM (  SELECT  id_account_credit id_account
                                                        , amount
                                                FROM cfe.ledger_account_entry
                                                    INNER JOIN ex
                                                        ON ledger_account_entry.posting_date <= ex.posting_date
                                                UNION ALL
                                                SELECT  id_account_debit
                                                        , - amount
                                                FROM cfe.ledger_account_entry
                                                    INNER JOIN ex
                                                        ON ledger_account_entry.posting_date <= ex.posting_date )
                                        GROUP BY id_account ) d
                            ON c.id_account = d.id_account
                        INNER JOIN fxr
                            ON c.id_currency = fxr.id_currency_from
                    GROUP BY b.code ) c
        ON c.code = a.code
;

-- INSERT INTO LEDGER BRANCH BALANCE NEW
WITH scope AS (
        SELECT *
        FROM cfe.accounting_scope
        WHERE id_status = 'C'
            AND id_accounting_scope_code = :SCOPE )
    , ex AS (
        SELECT *
        FROM cfe.execution
        WHERE id_status = 'R'
            AND value_date = (  SELECT Max( value_date )
                                FROM cfe.execution
                                WHERE id_status = 'R'
                                    AND ( :VALUE_DATE IS NULL
                                            OR value_date <= :VALUE_DATE ) ) )
    , fxr AS (
        SELECT  id_currency_from
                , fxrate
        FROM common.fxrate_hst f
            INNER JOIN ex
                ON f.value_date <= ex.value_date
        WHERE f.value_date = (  SELECT Max( value_date )
                                FROM common.fxrate_hst
                                WHERE id_currency_from = f.id_currency_from
                                    AND id_currency_into = f.id_currency_into
                                    AND value_date <= ex.value_date )
            AND id_currency_into = :BOOK_CURRENCY
        UNION ALL
        SELECT  :BOOK_CURRENCY
                , 1
        FROM dual )
SELECT /*+ PARALLEL */
    scope.id_accounting_scope
    , ex.value_date
    , ex.posting_date
    , a.gl_level
    , a.code
    , b.description
    , c.balance_bc
FROM ex
    , scope
    INNER JOIN cfe.ledger_branch_branch a
        ON a.id_accounting_scope = scope.id_accounting_scope
            AND a.code = a.code_inferior
    INNER JOIN cfe.ledger_branch b
        ON b.id_accounting_scope = scope.id_accounting_scope
            AND b.code = a.code
    INNER JOIN (    SELECT  b.code
                            , Round( d.amount * fxr.fxrate, 2 )
                    FROM scope
                        INNER JOIN cfe.ledger_branch_branch b
                            ON b.id_accounting_scope = scope.id_accounting_scope
                        INNER JOIN cfe.ledger_account c
                            ON b.code_inferior = c.code
                                AND c.id_accounting_scope_code = scope.id_accounting_scope_code
                        INNER JOIN (    SELECT  id_account
                                                , Sum( amount )
                                        FROM (  SELECT  id_account_credit
                                                        , amount
                                                FROM cfe.ledger_account_entry
                                                    INNER JOIN ex
                                                        ON ledger_account_entry.posting_date <= ex.posting_date
                                                UNION ALL
                                                SELECT  id_account_debit
                                                        , - amount
                                                FROM cfe.ledger_account_entry
                                                    INNER JOIN ex
                                                        ON ledger_account_entry.posting_date <= ex.posting_date ) ) d
                            ON c.id_account = d.id_account
                        INNER JOIN fxr
                            ON c.id_currency = fxr.id_currency_from ) c
        ON c.code = a.code
;

-- APPEND COLLATERAL REF
SELECT /*+ PARALLEL */
    cfe.id_collateral_ref.nextval
    , id_collateral
FROM (  SELECT DISTINCT
            a.id_collateral
        FROM cfe.collateral a
            LEFT JOIN cfe.collateral_ref b
                ON a.id_collateral = b.id_collateral
        WHERE b.id_collateral_ref IS NULL )
;

-- APPEND COUNTER PARTY REF
SELECT /*+ PARALLEL */
    cfe.id_counter_party_ref.nextval
    , id_counter_party
FROM (  SELECT DISTINCT
            a.id_counter_party
        FROM cfe.collateral a
            LEFT JOIN cfe.counter_party_ref b
                ON a.id_counter_party = b.id_counter_party
        WHERE a.id_counter_party IS NOT NULL
            AND b.id_counter_party_ref IS NULL )
;

-- APPEND COLLATERAL HST
SELECT /*+ PARALLEL */
    b.id_collateral_ref
    , c.id_counter_party_ref
    , coalesce valid_date
    , a.description
    , d.id_collateral_type_ref
    , a.fair_value
    , a.forced_sale_value
    , a.id_currency
    , a.appraisal_date
FROM cfe.collateral a
    INNER JOIN cfe.collateral_ref b
        ON a.id_collateral = b.id_collateral
    LEFT JOIN cfe.counter_party_ref c
        ON a.id_counter_party = c.id_counter_party
    INNER JOIN (    SELECT *
                    FROM common.collateral_type d1
                    WHERE id_status IN ( 'C', 'H' )
                        AND id_collateral_type_ref = (  SELECT Max( id_collateral_type_ref )
                                                        FROM common.collateral_type
                                                        WHERE id_status IN ( 'C', 'H' )
                                                            AND id_collateral_type = d1.id_collateral_type ) ) d
        ON a.id_collateral_type = d.id_collateral_type
;

-- SELECT WITH COMPLEX ORDER
WITH ex AS (
        SELECT  value_date
                , posting_date
        FROM cfe.execution x
        WHERE id_status IN ( 'R', 'H' )
            AND value_date = (  SELECT Max( value_date )
                                FROM cfe.execution
                                WHERE id_status IN ( 'R', 'H' ) )
            AND posting_date = (    SELECT Max( posting_date )
                                    FROM cfe.execution
                                    WHERE id_status IN ( 'R', 'H' )
                                        AND value_date = x.value_date ) )
    , fxr AS (
        SELECT  id_currency_from
                , fxrate
        FROM common.fxrate_hst f
        WHERE f.value_date <= ( SELECT value_date
                                FROM ex )
            AND f.value_date = (    SELECT Max( value_date )
                                    FROM common.fxrate_hst
                                    WHERE id_currency_from = f.id_currency_from
                                        AND id_currency_into = f.id_currency_into )
            AND id_currency_into = 'NGN'
        UNION ALL
        SELECT  'NGN'
                , 1
        FROM dual )
    , scope AS (
        SELECT *
        FROM cfe.accounting_scope
        WHERE id_status = 'C'
            AND id_accounting_scope_code = 'INTERN' )
    , scope1 AS (
        SELECT *
        FROM cfe.accounting_scope
        WHERE id_status = 'C'
            AND id_accounting_scope_code = 'NGAAP' )
    , c AS (
        SELECT  b.code
                , Round( d.amount * fxr.fxrate, 2 ) balance_bc
        FROM scope
            INNER JOIN cfe.ledger_branch_branch b
                ON b.id_accounting_scope = scope.id_accounting_scope
            INNER JOIN cfe.ledger_account c
                ON b.code_inferior = c.code
                    AND c.id_accounting_scope_code = scope.id_accounting_scope_code
            INNER JOIN (    SELECT  id_account_credit id_account
                                    , amount
                            FROM cfe.ledger_account_entry
                                INNER JOIN ex
                                    ON ledger_account_entry.posting_date <= ex.posting_date
                            UNION ALL
                            SELECT  id_account_debit
                                    , - amount
                            FROM cfe.ledger_account_entry
                                INNER JOIN ex
                                    ON ledger_account_entry.posting_date <= ex.posting_date ) d
                ON c.id_account = d.id_account
            INNER JOIN fxr
                ON c.id_currency = fxr.id_currency_from
        GROUP BY b.code )
    , c1 AS (
        SELECT  b.code
                , Round( d.amount * fxr.fxrate, 2 ) balance_bc
        FROM scope1
            INNER JOIN cfe.ledger_branch_branch b
                ON b.id_accounting_scope = scope1.id_accounting_scope
            INNER JOIN cfe.ledger_account c
                ON b.code_inferior = c.code
                    AND c.id_accounting_scope_code = scope1.id_accounting_scope_code
            INNER JOIN (    SELECT  id_account_credit id_account
                                    , amount
                            FROM cfe.ledger_account_entry
                                INNER JOIN ex
                                    ON ledger_account_entry.posting_date <= ex.posting_date
                            UNION ALL
                            SELECT  id_account_debit
                                    , - amount
                            FROM cfe.ledger_account_entry
                                INNER JOIN ex
                                    ON ledger_account_entry.posting_date <= ex.posting_date ) d
                ON c.id_account = d.id_account
            INNER JOIN fxr
                ON c.id_currency = fxr.id_currency_from
        GROUP BY b.code )
SELECT /*+ PARALLEL */
    a.code code
    , Lpad( ' ', 4 * ( a.gl_level - 1 ), ' ' )
             || a.code format_code
    , b.description
    , c.balance_bc
    , c1.balance_bc
FROM scope
    INNER JOIN cfe.ledger_branch_branch a
        ON a.code = a.code_inferior
            AND a.id_accounting_scope = scope.id_accounting_scope
    INNER JOIN cfe.ledger_branch b
        ON a.id_accounting_scope = b.id_accounting_scope
            AND a.code = b.code
    LEFT JOIN c
        ON a.code = c.code
    LEFT OUTER JOIN c1
        ON a.code = c1.code
WHERE gl_level <= 3
    AND NOT ( c.balance_bc IS NULL
                AND c1.balance_bc IS NULL )
ORDER BY    (   SELECT code
                FROM cfe.ledger_branch_branch
                WHERE id_accounting_scope = a.id_accounting_scope
                    AND code_inferior = a.code
                    AND gl_level = 1 ) NULLS FIRST
            , ( SELECT code
                FROM cfe.ledger_branch_branch
                WHERE id_accounting_scope = a.id_accounting_scope
                    AND code_inferior = a.code
                    AND gl_level = 2 ) ASC NULLS FIRST
            , ( SELECT code
                FROM cfe.ledger_branch_branch
                WHERE id_accounting_scope = a.id_accounting_scope
                    AND code_inferior = a.code
                    AND gl_level = 3 ) DESC NULLS FIRST
            , ( SELECT code
                FROM cfe.ledger_branch_branch
                WHERE id_accounting_scope = a.id_accounting_scope
                    AND code_inferior = a.code
                    AND gl_level = 4 ) DESC
            , ( SELECT code
                FROM cfe.ledger_branch_branch
                WHERE id_accounting_scope = a.id_accounting_scope
                    AND code_inferior = a.code
                    AND gl_level = 5 ) ASC
            , ( SELECT code
                FROM cfe.ledger_branch_branch
                WHERE id_accounting_scope = a.id_accounting_scope
                    AND code_inferior = a.code
                    AND gl_level = 6 ) NULLS FIRST
            , ( SELECT code
                FROM cfe.ledger_branch_branch
                WHERE id_accounting_scope = a.id_accounting_scope
                    AND code_inferior = a.code
                    AND gl_level = 7 ) NULLS FIRST
            , code
;

-- ALL COLUMNS FROM TABLE
SELECT a.*
FROM cfe.instrument a
;

-- NESTED WITH
WITH teststmt1 AS (
        WITH teststmt2 AS (
                SELECT *
                FROM my_table2 )
        SELECT  col1
                , col2
        FROM teststmt2 )
SELECT *
FROM teststmt
;

-- COMPLEX PARSING 1
SELECT To_Char( [travel_data].[travel_id] = '3' )
FROM dual
;

-- COMPLEX PARSING 2
SELECT ( a = '3' )
FROM dual
;

3.4. Merge

-- MERGE 1
MERGE INTO cfe.impairment imp
    USING ( WITH x AS (
                    SELECT  a.id_instrument
                            , a.id_currency
                            , a.id_instrument_type
                            , b.id_portfolio
                            , c.attribute_value product_code
                            , t.valid_date
                            , t.ccf
                    FROM cfe.instrument a
                        INNER JOIN cfe.impairment b
                            ON a.id_instrument = b.id_instrument
                        LEFT JOIN cfe.instrument_attribute c
                            ON a.id_instrument = c.id_instrument
                                AND c.id_attribute = 'product'
                        INNER JOIN cfe.ext_ccf t
                            ON ( a.id_currency LIKE t.id_currency )
                                AND ( a.id_instrument_type LIKE t.id_instrument_type )
                                AND ( b.id_portfolio LIKE t.id_portfolio
                                        OR ( b.id_portfolio IS NULL
                                                AND t.id_portfolio = '%' ) )
                                AND ( c.attribute_value LIKE t.product_code
                                        OR ( c.attribute_value IS NULL
                                                AND t.product_code = '%' ) ) )
SELECT /*+ PARALLEL */ *
            FROM x x1
            WHERE x1.valid_date = ( SELECT max
                                    FROM x
                                    WHERE id_instrument = x1.id_instrument ) ) s
        ON ( imp.id_instrument = s.id_instrument )
WHEN MATCHED THEN
    UPDATE SET  imp.ccf = s.ccf
;

-- MERGE 2
MERGE INTO cfe.instrument_import_measure imp
    USING ( WITH x AS (
                    SELECT  a.id_instrument
                            , a.id_currency
                            , a.id_instrument_type
                            , b.id_portfolio
                            , c.attribute_value product_code
                            , t.valid_date
                            , t.yield
                    FROM cfe.instrument a
                        INNER JOIN cfe.impairment b
                            ON a.id_instrument = b.id_instrument
                        LEFT JOIN cfe.instrument_attribute c
                            ON a.id_instrument = c.id_instrument
                                AND c.id_attribute = 'product'
                        INNER JOIN cfe.ext_yield t
                            ON ( a.id_currency = t.id_currency )
                                AND ( a.id_instrument_type LIKE t.id_instrument_type )
                                AND ( b.id_portfolio LIKE t.id_portfolio
                                        OR ( b.id_portfolio IS NULL
                                                AND t.id_portfolio = '%' ) )
                                AND ( c.attribute_value LIKE t.product_code
                                        OR ( c.attribute_value IS NULL
                                                AND t.product_code = '%' ) ) )
SELECT /*+ PARALLEL */ *
            FROM x x1
            WHERE x1.valid_date = ( SELECT max
                                    FROM x
                                    WHERE id_instrument = x1.id_instrument
                                        AND valid_date <= to_date ) ) s
        ON ( imp.id_instrument = s.id_instrument
                    AND imp.measure = 'YIELD' )
WHEN MATCHED THEN
    UPDATE SET  imp.value = s.yield
;

-- MERGE 3
MERGE INTO cfe.instrument_import_measure imp
    USING s
        ON ( imp.id_instrument = s.id_instrument
                    AND imp.measure = 'YIELD_P'
                    AND imp.id_instrument = s.id_instrument
                    AND imp.measure = 'YIELD_P' )
WHEN MATCHED THEN
    UPDATE SET  imp.value = s.yield
;

-- MERGE 4
MERGE INTO cfe.instrument_import_measure imp
    USING ( WITH x AS (
                    SELECT  a.id_instrument
                            , a.id_currency
                            , a.id_instrument_type
                            , b.id_portfolio
                            , c.attribute_value product_code
                            , t.valid_date
                            , t.yield
                    FROM cfe.instrument a
                        INNER JOIN cfe.impairment b
                            ON a.id_instrument = b.id_instrument
                        LEFT JOIN cfe.instrument_attribute c
                            ON a.id_instrument = c.id_instrument
                                AND c.id_attribute = 'product'
                        INNER JOIN cfe.ext_yield t
                            ON ( a.id_currency = t.id_currency )
                                AND ( a.id_instrument_type LIKE t.id_instrument_type )
                                AND ( b.id_portfolio LIKE t.id_portfolio
                                        OR ( b.id_portfolio IS NULL
                                                AND t.id_portfolio = '%' ) )
                                AND ( c.attribute_value LIKE t.product_code
                                        OR ( c.attribute_value IS NULL
                                                AND t.product_code = '%' ) ) )
SELECT /*+ PARALLEL */ *
            FROM x x1
            WHERE x1.valid_date = ( SELECT max
                                    FROM x
                                    WHERE id_instrument = x1.id_instrument
                                        AND valid_date <= to_date ) ) s
        ON ( imp.id_instrument = s.id_instrument
                    AND imp.measure = 'YIELD_PP' )
WHEN MATCHED THEN
    UPDATE SET  imp.value = s.yield
;

-- MERGE DELETE WHERE
MERGE INTO empl_current tar
    USING ( SELECT  empno
                    , ename
                    , CASE
                            WHEN leavedate <= sysdate
                                THEN 'Y'
                            ELSE 'N'
                        END AS delete_flag
            FROM empl ) src
        ON ( tar.empno = src.empno )
WHEN NOT MATCHED THEN
    INSERT ( empno
                , ename )
    VALUES ( src.empno
                , src.ename )
WHEN MATCHED THEN
    UPDATE SET  tar.ename = src.ename
    WHERE   delete_flag = 'N'
    DELETE WHERE    delete_flag = 'Y'
;

-- BOTH CLAUSES PRESENT
MERGE INTO test1 a
    USING all_objects
        ON ( a.object_id = b.object_id )
WHEN NOT MATCHED THEN
    INSERT ( object_id
                , status )
    VALUES ( b.object_id
                , b.status )
WHEN MATCHED THEN
    UPDATE SET  a.status = b.status
    WHERE   b.status != 'VALID'
;

-- BOTH CLAUSES PRESENT 2
MERGE INTO test1 a
    USING all_objects
        ON ( a.object_id = b.object_id )
WHEN NOT MATCHED THEN
    INSERT ( object_id
                , status )
    VALUES ( b.object_id
                , b.status )
    WHERE   b.status != 'VALID'
WHEN MATCHED THEN
    UPDATE SET  a.status = b.status
    WHERE   b.status != 'VALID'
;

-- INSERT WITHOUT COLUMNS
MERGE /*+ PARALLEL */ INTO cfe.tmp_eab a
    USING ( SELECT /*+ PARALLEL DRIVING_SITE(C) */ c.*
            FROM tbaadm.eab@finnacle c
                INNER JOIN (    SELECT  acid
                                        , eod_date
                                FROM cfe.tmp_eab e
                                WHERE end_eod_date = (  SELECT Max( eod_date )
                                                        FROM cfe.tmp_eab
                                                        WHERE acid = e.acid )
                                    AND end_eod_date < '31-Dec-2099' ) d
                    ON c.acid = d.acid
                        AND c.eod_date >= d.eod_date ) b
        ON ( a.acid = b.acid
                    AND a.eod_date = b.eod_date )
WHEN MATCHED THEN
    UPDATE SET  a.tran_date_bal = b.tran_date_bal
                , a.tran_date_tot_tran = b.tran_date_tot_tran
                , a.value_date_bal = b.value_date_bal
                , a.value_date_tot_tran = b.value_date_tot_tran
                , a.end_eod_date = b.end_eod_date
                , a.lchg_user_id = b.lchg_user_id
                , a.lchg_time = b.lchg_time
                , a.rcre_user_id = b.rcre_user_id
                , a.rcre_time = b.rcre_time
                , a.ts_cnt = b.ts_cnt
                , a.eab_crncy_code = b.eab_crncy_code
                , a.bank_id = b.bank_id
WHEN NOT MATCHED THEN
    INSERT VALUES ( b.acid
                    , b.eod_date
                    , b.tran_date_bal
                    , b.tran_date_tot_tran
                    , b.value_date_bal
                    , b.value_date_tot_tran
                    , b.end_eod_date
                    , b.lchg_user_id
                    , b.lchg_time
                    , b.rcre_user_id
                    , b.rcre_time
                    , b.ts_cnt
                    , b.eab_crncy_code
                    , b.bank_id )
;

3.5. Insert

-- INSERT COUNTERPARTY COUNTERPARTY RELATIONSHIP
INSERT INTO risk.counterparty_counterparty
VALUES (    :id_counterparty_ref, :id_counterparty_beneficiary, :id_instrument_guarantee
            , :priority, :type, :description
            , :limit_amout, :id_currency, :end_date )
;

-- INSERT RATIO COLLECTION RATIOS
INSERT INTO risk.counterparty_ratio
VALUES ( ?, ?, ? )
;

-- INSERT TMP_CCF
INSERT INTO risk.tmp_ccf (
    "ID_INSTRUMENT"
    , "TENOR"
    , "STATUS"
    , "OBSERVATION_DATE"
    , "BALANCE"
    , "LIMIT"
    , "DR_BALANCE"
    , "OPEN_LIMIT" )
SELECT  '1000042339'       /* ID_INSTRUMENT */
        , 0                /* TENOR */
        , 'DEFAULT'        /* STATUS */
        , {d '2020-02-27'} /* OBSERVATION_DATE */
        , - 142574953.65   /* BALANCE */
        , 300000000        /* LIMIT */
        , - 142574953.65   /* DR_BALANCE */
        , 157425046.35     /* OPEN_LIMIT */
FROM dual
;

-- APPEND ATTRIBUTE VALUE REF
INSERT INTO cfe.attribute_value_ref
SELECT  cfe.id_attribute_value_ref.nextval
        , attribute_value
FROM (  SELECT DISTINCT
            a.attribute_value
        FROM cfe.instrument_attribute a
            LEFT JOIN cfe.attribute_value_ref b
                ON a.attribute_value = b.attribute_value
        WHERE b.attribute_value IS NULL ) a
;

3.6. Update

-- UPDATE COUNTERPARTY
UPDATE risk.counterparty
SET id_counterparty = :id_counterparty
    , label = :label
    , description = :description
    , id_counterparty_group_type = :id_counterparty_group_type
    , id_counterparty_type = :id_counterparty_type
    , id_counterparty_sub_type = :id_counterparty_sub_type
    , id_country_group = :id_country_group
    , id_country = :id_country
    , id_country_state = :id_country_state
    , id_district = :id_district
    , id_city = :id_city
    , id_industrial_sector = :id_industrial_sector
    , id_industrial_sub_sector = :id_industrial_sub_sector
    , block_auto_update_flag = :block_auto_update_flag
    , id_user_editor = :id_user_editor
    , id_organization_unit = :id_organization_unit
    , id_status = :id_status
    , update_timestamp = current_timestamp
WHERE id_counterparty_ref = :id_counterparty_ref
;

-- UPDATE COLLATERAL_TYPE
UPDATE common.collateral_type
SET hair_cut = least
WHERE id_collateral_type_ref IN (   SELECT id_collateral_type_ref
                                    FROM common.collateral_type a
                                    WHERE id_status IN (    'C', 'H', 'C'
                                                            , 'H', 'C', 'H'
                                                            , 'C', 'H' )
                                        AND id_collateral_type_ref = (  SELECT Max( id_collateral_type_ref )
                                                                        FROM common.collateral_type
                                                                        WHERE id_status IN ( 'C', 'H' )
                                                                            AND id_collateral_type = a.id_collateral_type ) )
;

-- UPDATE COUNTERPARTY_INSTRUMENT
UPDATE risk.counterparty_instrument a1
SET (   priority
        , type
        , description
        , limit_amout
        , id_currency
        , end_date ) = (    SELECT  a.priority
                                    , a.type
                                    , a.description
                                    , a.limit_amout
                                    , a.id_currency
                                    , a.end_date
                            FROM risk.imp_counterparty_instrument a
                                INNER JOIN risk.counterparty b
                                    ON a.id_counterparty = b.id_counterparty
                                        AND b.id_status = 'C'
                                INNER JOIN risk.instrument c
                                    ON a.id_instrument_beneficiary = c.id_instrument
                                        AND c.id_status = 'C'
                                INNER JOIN risk.counterparty_instrument e
                                    ON b.id_counterparty_ref = e.id_counterparty_ref
                                        AND e.id_instrument_beneficiary = a.id_instrument_beneficiary
                                        AND e.id_instrument_guarantee = a.id_instrument_guarantee
                            WHERE e.id_counterparty_ref = a1.id_counterparty_ref
                                AND e.id_instrument_beneficiary = a1.id_instrument_beneficiary
                                AND e.id_instrument_guarantee = a1.id_instrument_guarantee )
WHERE EXISTS (  SELECT  a.priority
                        , a.type
                        , a.description
                        , a.limit_amout
                        , a.id_currency
                        , a.end_date
                FROM risk.imp_counterparty_instrument a
                    INNER JOIN risk.counterparty b
                        ON a.id_counterparty = b.id_counterparty
                            AND b.id_status = 'C'
                    INNER JOIN risk.instrument c
                        ON a.id_instrument_beneficiary = c.id_instrument
                            AND c.id_status = 'C'
                    INNER JOIN risk.counterparty_instrument e
                        ON b.id_counterparty_ref = e.id_counterparty_ref
                            AND e.id_instrument_beneficiary = a.id_instrument_beneficiary
                            AND e.id_instrument_guarantee = a.id_instrument_guarantee
                WHERE e.id_counterparty_ref = a1.id_counterparty_ref
                    AND e.id_instrument_beneficiary = a1.id_instrument_beneficiary
                    AND e.id_instrument_guarantee = a1.id_instrument_guarantee )
;

3.7. Comments

------------------------------------------------------------------------------------------------------------------------
-- CONFIGURATION
------------------------------------------------------------------------------------------------------------------------

-- UPDATE CALENDAR
UPDATE cfe.calendar
SET year_offset = ?            /* year offset */
    , settlement_shift = ?     /* settlement shift */
    , friday_is_holiday = ?    /* friday is a holiday */
    , saturday_is_holiday = ?  /* saturday is a holiday */
    , sunday_is_holiday = ?    /* sunday is a holiday */
WHERE id_calendar = ?
;


-- BOTH CLAUSES PRESENT 'with a string' AND "a field"
MERGE /*+ PARALLEL */ INTO test1 /*the target table*/ a
    USING all_objects      /*the source table*/
        ON ( /*joins in()!*/ a.object_id = b.object_id )
-- INSERT CLAUSE 
WHEN /*comments between keywords!*/ NOT MATCHED THEN
    INSERT ( object_id     /*ID Column*/
                , status   /*Status Column*/ )
    VALUES ( b.object_id
                , b.status )
/* UPDATE CLAUSE
WITH A WHERE CONDITION */ 
WHEN MATCHED THEN          /* Lets rock */
    UPDATE SET  a.status = '/*this is no comment!*/ and -- this ain''t either'
    WHERE   b."--status" != 'VALID'
;

3.8. MS Sql Server

-- BRACKETS 1
SELECT columnname
FROM [server-name\\server-instance]..schemaname.tablename
;

-- BRACKETS 2
SELECT columnname
FROM [server-name\\server-instance]..[schemaName].[table Name]
;

-- BRACKETS 3
SELECT columnname
FROM [server-name\\server-instance]..[schemaName].[table-Name]
;

-- BRACKETS 4
SELECT columnname
FROM [schemaName].[tableName]
;

-- BRACKETS 5
SELECT columnname
FROM schemaname.[tableName]
;

-- BRACKETS 6
SELECT columnname
FROM [schemaName].tablename
;

-- READ INSTRUMENT TRANSACTIONS WITH COLLATERAL ONLY
SELECT a.*
FROM [cfe].[TRANSACTION] a
    INNER JOIN cfe.instrument b
        ON a.id_instrument = b.id_instrument
WHERE a.id_instrument >= ?
    AND a.id_instrument <= ?
    AND EXISTS (    SELECT 1
                    FROM cfe.instrument_ref b
                        INNER JOIN cfe.instrument_collateral_hst c
                            ON b.id_instrument_ref = c.id_instrument_ref
                    WHERE b.id_instrument = a.id_instrument )
;

-- DELETE INSTRUMENT ATTRIBUTE HST AFTER VALUE_DATE_P
DELETE a FROM cfe.instrument_attribute_hst2 a
    INNER JOIN (    SELECT  value_date
                            , posting_date
                    FROM cfe.execution
                    WHERE posting_date > (  SELECT Max( posting_date )
                                            FROM cfe.execution
                                            WHERE id_status = 'R'
                                                AND value_date <= :value_date_p )
                        OR (    SELECT Max( posting_date )
                                FROM cfe.execution
                                WHERE id_status = 'R'
                                    AND value_date <= :value_date_p ) IS NULL ) b
        ON a.value_date = b.value_date
            AND b.posting_date = b.posting_date
;

-- READ INSTRUMENT TRANSACTIONS WITH COLLATERAL ONLY1
SELECT a.*
FROM [cfe].[TRANSACTION] a
    INNER JOIN cfe.instrument b
        ON a.id_instrument = b.id_instrument
WHERE a.id_instrument >= ?
    AND a.id_instrument <= ?
    AND EXISTS (    SELECT 1
                    FROM cfe.instrument_ref b
                        INNER JOIN cfe.instrument_collateral_hst c
                            ON b.id_instrument_ref = c.id_instrument_ref
                    WHERE b.id_instrument = a.id_instrument )
;

3.9. Formatting Options

-- 1 UPDATE CALENDAR
-- @JSQLFormatter(indentWidth=8, keywordSpelling=UPPER, functionSpelling=CAMEL, objectSpelling=LOWER, separation=BEFORE)
UPDATE cfe.calendar
SET     year_offset = ?                    /* year offset */
        , settlement_shift = To_Char( ? )  /* settlement shift */
        , friday_is_holiday = ?            /* friday is a holiday */
        , saturday_is_holiday = ?          /* saturday is a holiday */
        , sunday_is_holiday = ?            /* sunday is a holiday */
WHERE id_calendar = ?
;


-- 2 UPDATE CALENDAR
-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
update CFE.CALENDAR
set YEAR_OFFSET = ?                    /* year offset */,
    SETTLEMENT_SHIFT = to_char( ? )    /* settlement shift */,
    FRIDAY_IS_HOLIDAY = ?              /* friday is a holiday */,
    SATURDAY_IS_HOLIDAY = ?            /* saturday is a holiday */,
    SUNDAY_IS_HOLIDAY = ?              /* sunday is a holiday */
where ID_CALENDAR = ?
;


-- 3 MERGE DELETE WHERE
merge into EMPL_CURRENT TAR
  using ( select  EMPNO,
                  ENAME,
                  case
                      when LEAVEDATE <= SYSDATE
                        then 'Y'
                      else 'N'
                    end as DELETE_FLAG
          from EMPL ) SRC
    on ( TAR.EMPNO = SRC.EMPNO )
when not matched then
  insert ( EMPNO,
            ENAME )
  values ( SRC.EMPNO,
            SRC.ENAME )
when matched then
  update set  TAR.ENAME = SRC.ENAME
  where DELETE_FLAG = 'N'
  delete where  DELETE_FLAG = 'Y'
;