3. Samples¶
3.1. Create¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | -- 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 )
;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | -- 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
;
|
3.2. Alter¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | -- 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¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 | -- 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
;
|
3.4. Merge¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | -- 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'
;
|
3.5. Insert¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
-- 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" )
VALUES ( '1000042339'
, 0
, 'DEFAULT'
, {d '2020-02-27'}
, - 142574953.65
, 300000000
, - 142574953.65
, 157425046.35 )
;
|
3.6. Update¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | -- 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 )
;
|