GitHub

Samples

Every example below is a live test fixture from the JSQLFormatter test suite, so what you see here is exactly what the current build produces.

Tip

Want to try your own statement? The online demo runs the same code in the browser.

Select

Projections, joins, sub-selects and WITH clauses. Note how the field separator sits at the start of the line by default, which keeps a long column list readable and makes diffs line up when a column is added.

-- 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
;

-- UNION
(
    SELECT __time
    FROM traffic_protocol_stat_log
    LIMIT 1 )
UNION ALL (
    SELECT __time
    FROM traffic_protocol_stat_log
    ORDER BY __time
    LIMIT 1 )
;

-- GROUP BY
SELECT  a
        , b
        , c
        , Sum( d )
FROM t
GROUP BY    a
            , b
            , c
HAVING Sum( d ) > 0
    AND Count( * ) > 1
;

-- TOP clause
SELECT TOP 10
    qtysold
    , sellerid
FROM sales
ORDER BY    qtysold DESC
            , sellerid
;

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
;

-- INSERT WITH
WITH scope AS (
        SELECT *
        FROM cfe.accounting_scope
        WHERE id_status = 'C'
            AND id_accounting_scope_code = :SCOPE )
INSERT INTO cfe.accounting_scope a
SELECT *
FROM scope
;

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 )
;

-- UPDATE SETS ISSUE 1316
UPDATE prpjpaymentbill b
SET (   b.packagecode
        , b.packageremark
        , b.agentcode ) = ( SELECT  p.payrefreason
                                    , p.classcode
                                    , p.riskcode
                            FROM prpjcommbill p
                            WHERE p.policertiid = 'SDDH200937010330006366' )   /* this is supposed to be UpdateSet 1 */
    , b.payrefnotype = '05'                                                    /* this is supposed to be UpdateSet 2 */
    , b.packageunit = '4101170402'                                             /* this is supposed to be UpdateSet 3 */
WHERE b.payrefno = 'B370202091026000005'
;

-- UPDATE START JOINS
UPDATE sc_borrower b
    INNER JOIN sc_credit_apply a
        ON a.borrower_id = b.id
SET b.name = '0.7505105896846266'
    , a.credit_line = a.credit_line + 1
WHERE b.id = 3
;

-- UPDATE JOINS
UPDATE table1
SET columna = 5
FROM table1
    LEFT JOIN table2
        ON col1 = col2
;

-- UPDATE WITH
WITH s AS (
        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 )
UPDATE risk.counterparty_instrument a1
SET (   priority
        , type
        , description
        , limit_amout
        , id_currency
        , end_date ) = (    SELECT *
                            FROM s )
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 )
;

Merge

MERGE is where regex-based formatters usually give up: the WHEN MATCHED and WHEN NOT MATCHED branches each carry a nested statement.

-- 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 )
;

-- MERGE WITH
WITH wmachine AS (
        SELECT DISTINCT
            projcode
            , plantcode
            , buildingcode
            , floorcode
            , room
        FROM tab_machinelocation
        WHERE Trim( Room ) <> ''
            AND Trim( Room ) <> '-' )
MERGE INTO tab_roomlocation AS troom
    USING wmachine
        ON ( troom.projcode = wmachine.projcode
                AND troom.plantcode = wmachine.plantcode
                AND troom.buildingcode = wmachine.buildingcode
                AND troom.floorcode = wmachine.floorcode
                AND troom.room = wmachine.room )
WHEN NOT MATCHED /* BY TARGET */ THEN
    INSERT ( projcode
                , plantcode
                , buildingcode
                , floorcode
                , room )
    VALUES ( wmachine.projcode
                , wmachine.plantcode
                , wmachine.buildingcode
                , wmachine.floorcode
                , wmachine.room )
OUTPUT  Getdate() AS timeaction
        , $action AS action
        , inserted.projcode
        , inserted.plantcode
        , inserted.buildingcode
        , inserted.floorcode
        , inserted.room
    INTO tab_mergeactions_roomlocation
;

Create

Table and index definitions, including column constraints and storage clauses.

-- 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
;

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
;

-- ORACLE DROP MULTIPLE COLUMNS
ALTER TABLE risk.collateral
    DROP (id_status, city )
;

-- ORACLE DROP MANY MULTIPLE COLUMNS
ALTER TABLE risk.collateral
    DROP (  id_status
            , city
            , column3
            , column4 )
;

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

-- ALTER TABLE ALTER 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
;

-- issue14
ALTER TABLE table1
    RENAME TO table2
;

Comments

Comments are anchored to the node they belong to rather than to a line number, so they survive re-indentation – including comments wedged between keywords or inside a join condition. String literals that merely look like comments are passed through untouched.

------------------------------------------------------------------------------------------------------------------------
-- 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'
;

MS SQL Server

T-SQL uses square brackets as identifier quotes, where other dialects use them for arrays. squareBracketQuotation controls the interpretation, and AUTO infers it from the statement.

-- 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 )
;

Formatting options

The same statements rendered under different option sets, showing the effect of indent width, separator position and the three spelling policies.

-- 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
-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
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'
;

On this page

• Samples
  • Select
  • Insert
  • Update
  • Merge
  • Create
  • Alter
  • Comments
  • MS SQL Server
  • Formatting options