Samples

Create

  1-- CREATE TABLE CFE.INTEREST_PERIOD
  2CREATE TABLE cfe.interest_period (
  3    id_instrument                VARCHAR (40)    NOT NULL
  4    , id_fixingmode              NUMBER (5)      DEFAULT 0 NOT NULL
  5    , fixing_date                DATE
  6    , change_date                DATE
  7    , base_rate                  VARCHAR (12)
  8    , base_margin_rate           DECIMAL (12,9)
  9    , par_rate                   VARCHAR (12)
 10    , par_margin_rate            DECIMAL (12,9)
 11    , id_payment_convention      VARCHAR (12)
 12    , id_day_count_convention    VARCHAR (12)
 13    , id_day_incl_convention     VARCHAR (12)
 14    , fix_amount                 DECIMAL (23,5)
 15    , id_currency_fix_amount     VARCHAR (3)
 16    , id_script                  VARCHAR (12)
 17)
 18;
 19
 20-- SIMPLE
 21CREATE TABLE employees (
 22    employee_number      INT         NOT NULL
 23    , employee_name      CHAR (50)   NOT NULL
 24    , department_id      INT
 25    , salary             INT
 26    , PRIMARY KEY ( employee_number )
 27    , UNIQUE ( employee_name )
 28    , FOREIGN KEY ( department_id )
 29        REFERENCES departments ( department_id )
 30) PARALLEL COMPRESS NOLOGGING
 31;
 32
 33-- COMPLEX
 34CREATE TABLE employees (
 35    employee_number      INT         NOT NULL
 36    , employee_name      CHAR (50)   NOT NULL
 37    , department_id      INT
 38    , salary             INT
 39    , CONSTRAINT employees_pk
 40        PRIMARY KEY ( employee_number )
 41    , CONSTRAINT fk_departments
 42        FOREIGN KEY ( department_id )
 43        REFERENCES departments ( department_id )
 44) PARALLEL COMPRESS NOLOGGING
 45;
 46
 47-- COMPLEX WITH MANY REFERENCES
 48CREATE TABLE employees (
 49    employee_number      INT         NOT NULL
 50    , employee_name      CHAR (50)   NOT NULL
 51    , department_id      INT
 52    , salary             INT
 53    , CONSTRAINT employees_pk
 54        PRIMARY KEY (   employee_number
 55                        , employee_name
 56                        , department_id )
 57    , CONSTRAINT fk_departments
 58        FOREIGN KEY (   employee_number
 59                        , employee_name
 60                        , department_id )
 61        REFERENCES departments (    employee_number
 62                                    , employee_name
 63                                    , department_id )
 64) PARALLEL COMPRESS NOLOGGING
 65;
 66
 67-- CREATE TABLE CFE.RECONCILIATION_NOMINAL_HST 2
 68CREATE TABLE cfe.reconciliation_nominal_hst PARALLEL COMPRESS NOLOGGING
 69    AS (    SELECT /*+ PARALLEL */
 70                (   SELECT id_execution_ref
 71                    FROM cfe.execution_ref c
 72                        INNER JOIN cfe.execution_v d
 73                            ON c.value_date = d.value_date
 74                                AND c.posting_date = d.posting_date
 75                                AND d.flag = 'L' ) id_execution_ref
 76                , b.id_instrument_ref
 77                , a.value_date
 78                , a.nominal_balance
 79            FROM cfe.reconciliation_nominal a
 80                INNER JOIN cfe.instrument_ref b
 81                    ON a.id_instrument = b.id_instrument )
 82;
 83
 84-- Z COMPLEX WITH MANY REFERENCES
 85-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
 86create table EMPLOYEES (
 87  EMPLOYEE_NUMBER    int       not null,
 88  EMPLOYEE_NAME      char (50) not null,
 89  DEPARTMENT_ID      int,
 90  SALARY             int,
 91  constraint EMPLOYEES_PK
 92    primary key ( EMPLOYEE_NUMBER,
 93                  EMPLOYEE_NAME,
 94                  DEPARTMENT_ID ),
 95  constraint FK_DEPARTMENTS
 96    foreign key ( EMPLOYEE_NUMBER,
 97                  EMPLOYEE_NAME,
 98                  DEPARTMENT_ID )
 99    references DEPARTMENTS (  EMPLOYEE_NUMBER,
100                              EMPLOYEE_NAME,
101                              DEPARTMENT_ID )
102) parallel compress nologging
103;
 1-- SIMPLE
 2CREATE INDEX cfe.version_info_idx1
 3    ON cfe.version_info(    major_version
 4                            , minor_version
 5                            , patch_level )
 6;
 7
 8-- UNIQUE
 9CREATE UNIQUE INDEX cfe.interest_period_idx1
10    ON cfe.interest_period( id_instrument, change_date )
11;
12
13-- MANY COLUMNS
14CREATE UNIQUE INDEX cfe.version_info_idx2
15    ON cfe.version_info(    major_version
16                            , minor_version
17                            , patch_level
18                            , major_version
19                            , minor_version
20                            , patch_level )
21;
22
23-- MANY COLUMNS WITH TAIL OPTIONS
24CREATE UNIQUE INDEX cfe.version_info_idx2
25    ON cfe.version_info(    major_version
26                            , minor_version
27                            , patch_level
28                            , major_version
29                            , minor_version
30                            , patch_level ) PARALLEL COMPRESS NOLOGGING
31;
32
33
34-- Z MANY COLUMNS WITH TAIL OPTIONS
35-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
36create unique index CFE.VERSION_INFO_IDX2
37  on CFE.VERSION_INFO(  MAJOR_VERSION,
38                        MINOR_VERSION,
39                        PATCH_LEVEL,
40                        MAJOR_VERSION,
41                        MINOR_VERSION,
42                        PATCH_LEVEL ) parallel compress nologging
43;

Alter

 1-- ALTER TABLE ADD FOREIGN KEY
 2ALTER TABLE cfe.ledger_acc_entry_manual
 3    ADD FOREIGN KEY (id_manual_posting_batch)
 4        REFERENCES manual_posting_batch (id_manual_posting_batch)
 5;
 6
 7-- ALTER TABLE DROP COLUMN
 8ALTER TABLE risk.collateral
 9    DROP COLUMN id_status
10;
11
12-- ORACLE DROP MULTIPLE COLUMNS
13ALTER TABLE risk.collateral
14    DROP (id_status, city )
15;
16
17-- ORACLE DROP MANY MULTIPLE COLUMNS
18ALTER TABLE risk.collateral
19    DROP (  id_status
20            , city
21            , column3
22            , column4 )
23;
24
25-- ALTER TABLE ADD COLUMN
26ALTER TABLE risk.collateral
27    ADD COLUMN id_status VARCHAR (1) NULL
28;
29
30-- ALTER TABLE ALTER COLUMN
31ALTER TABLE risk.collateral
32    ALTER COLUMN id_status VARCHAR (1) NOT NULL
33;
34
35-- ORACLE ALTER TABLE ADD COLUMN
36ALTER TABLE risk.collateral
37    ADD id_status VARCHAR (1) NULL
38;
39
40-- ORACLE ALTER TABLE MODIFY
41ALTER TABLE risk.collateral
42    MODIFY id_status VARCHAR (1) NOT NULL
43;
44
45-- ORACLE ADD MULTIPLE COLUMNS
46ALTER TABLE customers
47    ADD (   customer_name    VARCHAR2 (45)
48            , city           VARCHAR2 (40)   DEFAULT 'SEATTLE')
49;
50
51-- ORACLE MODIFY MULTIPLE COLUMNS
52ALTER TABLE customers
53    MODIFY (    customer_name    VARCHAR2 (100)  NOT NULL
54                , city           VARCHAR2 (75)   DEFAULT 'SEATTLE' NOT NULL)
55;
56
57-- RENAME
58ALTER TABLE departments
59    RENAME COLUMN department_name TO dept_name
60;
61
62-- issue14
63ALTER TABLE table1
64    RENAME TO table2
65;

Select

  1-- INSERT NEW LEDGER ACCOUNTS
  2SELECT /*+ PARALLEL */
  3    cfe.id_account_seq.nextval
  4    , "a"."code"
  5    , a."id_currency"
  6    , a.id_fee_type
  7    , current_date
  8    , NULL
  9    , id_accounting_scope_code
 10FROM (  SELECT *
 11        FROM (  SELECT DISTINCT
 12                    c.code code
 13                    , d.id_currency
 14                    , NULL id_fee_type
 15                    , c1.id_accounting_scope_code
 16                FROM cfe.ledger_branch c
 17                    INNER JOIN cfe.accounting_scope c1
 18                        ON c1.id_accounting_scope = c.id_accounting_scope
 19                            AND c1.id_status = 'C'
 20                    , common.ledger_currency d
 21                MINUS
 22                SELECT DISTINCT
 23                    c.code
 24                    , d.id_currency
 25                    , NULL id_fee_type
 26                    , c.id_accounting_scope_code
 27                FROM cfe.ledger_account c
 28                    INNER JOIN common.ledger_currency d
 29                        ON c.id_currency = d.id_currency )
 30        UNION
 31        SELECT *
 32        FROM (  SELECT DISTINCT
 33                    c.code
 34                    , d.id_currency
 35                    , NULL id_fee_type
 36                    , c1.id_accounting_scope_code
 37                FROM cfe.ledger_branch c
 38                    INNER JOIN cfe.accounting_scope c1
 39                        ON c1.id_accounting_scope = c.id_accounting_scope
 40                            AND c1.id_status = 'C'
 41                    , common.ledger_currency d
 42                MINUS
 43                SELECT DISTINCT
 44                    c.code
 45                    , d.id_currency
 46                    , NULL id_fee_type
 47                    , c.id_accounting_scope_code
 48                FROM cfe.ledger_account c
 49                    INNER JOIN common.ledger_currency d
 50                        ON c.id_currency = d.id_currency )
 51        UNION
 52        SELECT *
 53        FROM (  SELECT DISTINCT
 54                    c.code code
 55                    , d.id_currency
 56                    , e.id_fee_type
 57                    , c1.id_accounting_scope_code
 58                FROM cfe.ledger_branch c
 59                    INNER JOIN cfe.accounting_scope c1
 60                        ON c1.id_accounting_scope = c.id_accounting_scope
 61                            AND c1.id_status = 'C'
 62                    , common.ledger_currency d
 63                    , cfe.fee_type e
 64                MINUS
 65                SELECT DISTINCT
 66                    c.code
 67                    , d.id_currency
 68                    , e.id_fee_type
 69                    , c.id_accounting_scope_code
 70                FROM cfe.ledger_account c
 71                    INNER JOIN common.ledger_currency d
 72                        ON c.id_currency = d.id_currency
 73                    INNER JOIN cfe.fee_type e
 74                        ON c.id_fee_type = e.id_fee_type ) ) a
 75;
 76
 77-- INSERT INTO LEDGER BRANCH BALANCE
 78WITH scope AS (
 79        SELECT *
 80        FROM cfe.accounting_scope
 81        WHERE id_status = 'C'
 82            AND id_accounting_scope_code = :SCOPE )
 83    , ex AS (
 84        SELECT *
 85        FROM cfe.execution
 86        WHERE id_status = 'R'
 87            AND value_date = (  SELECT Max( value_date )
 88                                FROM cfe.execution
 89                                WHERE id_status = 'R'
 90                                    AND ( :VALUE_DATE IS NULL
 91                                            OR value_date <= :VALUE_DATE ) ) )
 92    , fxr AS (
 93        SELECT  id_currency_from
 94                , fxrate
 95        FROM common.fxrate_hst f
 96            INNER JOIN ex
 97                ON f.value_date <= ex.value_date
 98        WHERE f.value_date = (  SELECT Max( value_date )
 99                                FROM common.fxrate_hst
100                                WHERE id_currency_from = f.id_currency_from
101                                    AND id_currency_into = f.id_currency_into
102                                    AND value_date <= ex.value_date )
103            AND id_currency_into = :BOOK_CURRENCY
104        UNION ALL
105        SELECT  :BOOK_CURRENCY
106                , 1
107        FROM dual )
108SELECT /*+ PARALLEL */
109    scope.id_accounting_scope
110    , ex.value_date
111    , ex.posting_date
112    , a.gl_level
113    , a.code
114    , b.description
115    , c.balance_bc
116FROM ex
117    , scope
118    INNER JOIN cfe.ledger_branch_branch a
119        ON a.id_accounting_scope = scope.id_accounting_scope
120            AND a.code = a.code_inferior
121    INNER JOIN cfe.ledger_branch b
122        ON b.id_accounting_scope = scope.id_accounting_scope
123            AND b.code = a.code
124    INNER JOIN (    SELECT  b.code
125                            , Round( d.amount * fxr.fxrate, 2 ) balance_bc
126                    FROM scope
127                        INNER JOIN cfe.ledger_branch_branch b
128                            ON b.id_accounting_scope = scope.id_accounting_scope
129                        INNER JOIN cfe.ledger_account c
130                            ON b.code_inferior = c.code
131                                AND c.id_accounting_scope_code = scope.id_accounting_scope_code
132                        INNER JOIN (    SELECT  id_account
133                                                , Sum( amount ) balance
134                                        FROM (  SELECT  id_account_credit id_account
135                                                        , amount
136                                                FROM cfe.ledger_account_entry
137                                                    INNER JOIN ex
138                                                        ON ledger_account_entry.posting_date <= ex.posting_date
139                                                UNION ALL
140                                                SELECT  id_account_debit
141                                                        , - amount
142                                                FROM cfe.ledger_account_entry
143                                                    INNER JOIN ex
144                                                        ON ledger_account_entry.posting_date <= ex.posting_date )
145                                        GROUP BY id_account ) d
146                            ON c.id_account = d.id_account
147                        INNER JOIN fxr
148                            ON c.id_currency = fxr.id_currency_from
149                    GROUP BY b.code ) c
150        ON c.code = a.code
151;
152
153-- INSERT INTO LEDGER BRANCH BALANCE NEW
154WITH scope AS (
155        SELECT *
156        FROM cfe.accounting_scope
157        WHERE id_status = 'C'
158            AND id_accounting_scope_code = :SCOPE )
159    , ex AS (
160        SELECT *
161        FROM cfe.execution
162        WHERE id_status = 'R'
163            AND value_date = (  SELECT Max( value_date )
164                                FROM cfe.execution
165                                WHERE id_status = 'R'
166                                    AND ( :VALUE_DATE IS NULL
167                                            OR value_date <= :VALUE_DATE ) ) )
168    , fxr AS (
169        SELECT  id_currency_from
170                , fxrate
171        FROM common.fxrate_hst f
172            INNER JOIN ex
173                ON f.value_date <= ex.value_date
174        WHERE f.value_date = (  SELECT Max( value_date )
175                                FROM common.fxrate_hst
176                                WHERE id_currency_from = f.id_currency_from
177                                    AND id_currency_into = f.id_currency_into
178                                    AND value_date <= ex.value_date )
179            AND id_currency_into = :BOOK_CURRENCY
180        UNION ALL
181        SELECT  :BOOK_CURRENCY
182                , 1
183        FROM dual )
184SELECT /*+ PARALLEL */
185    scope.id_accounting_scope
186    , ex.value_date
187    , ex.posting_date
188    , a.gl_level
189    , a.code
190    , b.description
191    , c.balance_bc
192FROM ex
193    , scope
194    INNER JOIN cfe.ledger_branch_branch a
195        ON a.id_accounting_scope = scope.id_accounting_scope
196            AND a.code = a.code_inferior
197    INNER JOIN cfe.ledger_branch b
198        ON b.id_accounting_scope = scope.id_accounting_scope
199            AND b.code = a.code
200    INNER JOIN (    SELECT  b.code
201                            , Round( d.amount * fxr.fxrate, 2 )
202                    FROM scope
203                        INNER JOIN cfe.ledger_branch_branch b
204                            ON b.id_accounting_scope = scope.id_accounting_scope
205                        INNER JOIN cfe.ledger_account c
206                            ON b.code_inferior = c.code
207                                AND c.id_accounting_scope_code = scope.id_accounting_scope_code
208                        INNER JOIN (    SELECT  id_account
209                                                , Sum( amount )
210                                        FROM (  SELECT  id_account_credit
211                                                        , amount
212                                                FROM cfe.ledger_account_entry
213                                                    INNER JOIN ex
214                                                        ON ledger_account_entry.posting_date <= ex.posting_date
215                                                UNION ALL
216                                                SELECT  id_account_debit
217                                                        , - amount
218                                                FROM cfe.ledger_account_entry
219                                                    INNER JOIN ex
220                                                        ON ledger_account_entry.posting_date <= ex.posting_date ) ) d
221                            ON c.id_account = d.id_account
222                        INNER JOIN fxr
223                            ON c.id_currency = fxr.id_currency_from ) c
224        ON c.code = a.code
225;
226
227-- APPEND COLLATERAL REF
228SELECT /*+ PARALLEL */
229    cfe.id_collateral_ref.nextval
230    , id_collateral
231FROM (  SELECT DISTINCT
232            a.id_collateral
233        FROM cfe.collateral a
234            LEFT JOIN cfe.collateral_ref b
235                ON a.id_collateral = b.id_collateral
236        WHERE b.id_collateral_ref IS NULL )
237;
238
239-- APPEND COUNTER PARTY REF
240SELECT /*+ PARALLEL */
241    cfe.id_counter_party_ref.nextval
242    , id_counter_party
243FROM (  SELECT DISTINCT
244            a.id_counter_party
245        FROM cfe.collateral a
246            LEFT JOIN cfe.counter_party_ref b
247                ON a.id_counter_party = b.id_counter_party
248        WHERE a.id_counter_party IS NOT NULL
249            AND b.id_counter_party_ref IS NULL )
250;
251
252-- APPEND COLLATERAL HST
253SELECT /*+ PARALLEL */
254    b.id_collateral_ref
255    , c.id_counter_party_ref
256    , coalesce valid_date
257    , a.description
258    , d.id_collateral_type_ref
259    , a.fair_value
260    , a.forced_sale_value
261    , a.id_currency
262    , a.appraisal_date
263FROM cfe.collateral a
264    INNER JOIN cfe.collateral_ref b
265        ON a.id_collateral = b.id_collateral
266    LEFT JOIN cfe.counter_party_ref c
267        ON a.id_counter_party = c.id_counter_party
268    INNER JOIN (    SELECT *
269                    FROM common.collateral_type d1
270                    WHERE id_status IN ( 'C', 'H' )
271                        AND id_collateral_type_ref = (  SELECT Max( id_collateral_type_ref )
272                                                        FROM common.collateral_type
273                                                        WHERE id_status IN ( 'C', 'H' )
274                                                            AND id_collateral_type = d1.id_collateral_type ) ) d
275        ON a.id_collateral_type = d.id_collateral_type
276;
277
278-- SELECT WITH COMPLEX ORDER
279WITH ex AS (
280        SELECT  value_date
281                , posting_date
282        FROM cfe.execution x
283        WHERE id_status IN ( 'R', 'H' )
284            AND value_date = (  SELECT Max( value_date )
285                                FROM cfe.execution
286                                WHERE id_status IN ( 'R', 'H' ) )
287            AND posting_date = (    SELECT Max( posting_date )
288                                    FROM cfe.execution
289                                    WHERE id_status IN ( 'R', 'H' )
290                                        AND value_date = x.value_date ) )
291    , fxr AS (
292        SELECT  id_currency_from
293                , fxrate
294        FROM common.fxrate_hst f
295        WHERE f.value_date <= ( SELECT value_date
296                                FROM ex )
297            AND f.value_date = (    SELECT Max( value_date )
298                                    FROM common.fxrate_hst
299                                    WHERE id_currency_from = f.id_currency_from
300                                        AND id_currency_into = f.id_currency_into )
301            AND id_currency_into = 'NGN'
302        UNION ALL
303        SELECT  'NGN'
304                , 1
305        FROM dual )
306    , scope AS (
307        SELECT *
308        FROM cfe.accounting_scope
309        WHERE id_status = 'C'
310            AND id_accounting_scope_code = 'INTERN' )
311    , scope1 AS (
312        SELECT *
313        FROM cfe.accounting_scope
314        WHERE id_status = 'C'
315            AND id_accounting_scope_code = 'NGAAP' )
316    , c AS (
317        SELECT  b.code
318                , Round( d.amount * fxr.fxrate, 2 ) balance_bc
319        FROM scope
320            INNER JOIN cfe.ledger_branch_branch b
321                ON b.id_accounting_scope = scope.id_accounting_scope
322            INNER JOIN cfe.ledger_account c
323                ON b.code_inferior = c.code
324                    AND c.id_accounting_scope_code = scope.id_accounting_scope_code
325            INNER JOIN (    SELECT  id_account_credit id_account
326                                    , amount
327                            FROM cfe.ledger_account_entry
328                                INNER JOIN ex
329                                    ON ledger_account_entry.posting_date <= ex.posting_date
330                            UNION ALL
331                            SELECT  id_account_debit
332                                    , - amount
333                            FROM cfe.ledger_account_entry
334                                INNER JOIN ex
335                                    ON ledger_account_entry.posting_date <= ex.posting_date ) d
336                ON c.id_account = d.id_account
337            INNER JOIN fxr
338                ON c.id_currency = fxr.id_currency_from
339        GROUP BY b.code )
340    , c1 AS (
341        SELECT  b.code
342                , Round( d.amount * fxr.fxrate, 2 ) balance_bc
343        FROM scope1
344            INNER JOIN cfe.ledger_branch_branch b
345                ON b.id_accounting_scope = scope1.id_accounting_scope
346            INNER JOIN cfe.ledger_account c
347                ON b.code_inferior = c.code
348                    AND c.id_accounting_scope_code = scope1.id_accounting_scope_code
349            INNER JOIN (    SELECT  id_account_credit id_account
350                                    , amount
351                            FROM cfe.ledger_account_entry
352                                INNER JOIN ex
353                                    ON ledger_account_entry.posting_date <= ex.posting_date
354                            UNION ALL
355                            SELECT  id_account_debit
356                                    , - amount
357                            FROM cfe.ledger_account_entry
358                                INNER JOIN ex
359                                    ON ledger_account_entry.posting_date <= ex.posting_date ) d
360                ON c.id_account = d.id_account
361            INNER JOIN fxr
362                ON c.id_currency = fxr.id_currency_from
363        GROUP BY b.code )
364SELECT /*+ PARALLEL */
365    a.code code
366    , Lpad( ' ', 4 * ( a.gl_level - 1 ), ' ' )
367             || a.code format_code
368    , b.description
369    , c.balance_bc
370    , c1.balance_bc
371FROM scope
372    INNER JOIN cfe.ledger_branch_branch a
373        ON a.code = a.code_inferior
374            AND a.id_accounting_scope = scope.id_accounting_scope
375    INNER JOIN cfe.ledger_branch b
376        ON a.id_accounting_scope = b.id_accounting_scope
377            AND a.code = b.code
378    LEFT JOIN c
379        ON a.code = c.code
380    LEFT OUTER JOIN c1
381        ON a.code = c1.code
382WHERE gl_level <= 3
383    AND NOT ( c.balance_bc IS NULL
384                AND c1.balance_bc IS NULL )
385ORDER BY    (   SELECT code
386                FROM cfe.ledger_branch_branch
387                WHERE id_accounting_scope = a.id_accounting_scope
388                    AND code_inferior = a.code
389                    AND gl_level = 1 ) NULLS FIRST
390            , ( SELECT code
391                FROM cfe.ledger_branch_branch
392                WHERE id_accounting_scope = a.id_accounting_scope
393                    AND code_inferior = a.code
394                    AND gl_level = 2 ) ASC NULLS FIRST
395            , ( SELECT code
396                FROM cfe.ledger_branch_branch
397                WHERE id_accounting_scope = a.id_accounting_scope
398                    AND code_inferior = a.code
399                    AND gl_level = 3 ) DESC NULLS FIRST
400            , ( SELECT code
401                FROM cfe.ledger_branch_branch
402                WHERE id_accounting_scope = a.id_accounting_scope
403                    AND code_inferior = a.code
404                    AND gl_level = 4 ) DESC
405            , ( SELECT code
406                FROM cfe.ledger_branch_branch
407                WHERE id_accounting_scope = a.id_accounting_scope
408                    AND code_inferior = a.code
409                    AND gl_level = 5 ) ASC
410            , ( SELECT code
411                FROM cfe.ledger_branch_branch
412                WHERE id_accounting_scope = a.id_accounting_scope
413                    AND code_inferior = a.code
414                    AND gl_level = 6 ) NULLS FIRST
415            , ( SELECT code
416                FROM cfe.ledger_branch_branch
417                WHERE id_accounting_scope = a.id_accounting_scope
418                    AND code_inferior = a.code
419                    AND gl_level = 7 ) NULLS FIRST
420            , code
421;
422
423-- ALL COLUMNS FROM TABLE
424SELECT a.*
425FROM cfe.instrument a
426;
427
428-- NESTED WITH
429WITH teststmt1 AS (
430        WITH teststmt2 AS (
431                SELECT *
432                FROM my_table2 )
433        SELECT  col1
434                , col2
435        FROM teststmt2 )
436SELECT *
437FROM teststmt
438;
439
440(
441    SELECT __time
442    FROM traffic_protocol_stat_log
443    LIMIT 1 )
444UNION ALL (
445    SELECT __time
446    FROM traffic_protocol_stat_log
447    ORDER BY __time
448    LIMIT 1 )
449;
450
451-- GROUP BY
452SELECT  a
453        , b
454        , c
455        , Sum( d )
456FROM t
457GROUP BY    a
458            , b
459            , c
460HAVING Sum( d ) > 0
461    AND Count( * ) > 1
462;
463
464-- TOP clause
465SELECT TOP 10
466    qtysold
467    , sellerid
468FROM sales
469ORDER BY    qtysold DESC
470            , sellerid
471;

Merge

  1-- MERGE 1
  2MERGE INTO cfe.impairment imp
  3    USING ( WITH x AS (
  4                    SELECT  a.id_instrument
  5                            , a.id_currency
  6                            , a.id_instrument_type
  7                            , b.id_portfolio
  8                            , c.attribute_value product_code
  9                            , t.valid_date
 10                            , t.ccf
 11                    FROM cfe.instrument a
 12                        INNER JOIN cfe.impairment b
 13                            ON a.id_instrument = b.id_instrument
 14                        LEFT JOIN cfe.instrument_attribute c
 15                            ON a.id_instrument = c.id_instrument
 16                                AND c.id_attribute = 'product'
 17                        INNER JOIN cfe.ext_ccf t
 18                            ON ( a.id_currency LIKE t.id_currency )
 19                                AND ( a.id_instrument_type LIKE t.id_instrument_type )
 20                                AND ( b.id_portfolio LIKE t.id_portfolio
 21                                        OR ( b.id_portfolio IS NULL
 22                                                AND t.id_portfolio = '%' ) )
 23                                AND ( c.attribute_value LIKE t.product_code
 24                                        OR ( c.attribute_value IS NULL
 25                                                AND t.product_code = '%' ) ) )
 26            SELECT /*+ PARALLEL */
 27                *
 28            FROM x x1
 29            WHERE x1.valid_date = ( SELECT max
 30                                    FROM x
 31                                    WHERE id_instrument = x1.id_instrument ) ) s
 32        ON ( imp.id_instrument = s.id_instrument )
 33WHEN MATCHED THEN
 34    UPDATE SET  imp.ccf = s.ccf
 35;
 36
 37-- MERGE 2
 38MERGE INTO cfe.instrument_import_measure imp
 39    USING ( WITH x AS (
 40                    SELECT  a.id_instrument
 41                            , a.id_currency
 42                            , a.id_instrument_type
 43                            , b.id_portfolio
 44                            , c.attribute_value product_code
 45                            , t.valid_date
 46                            , t.yield
 47                    FROM cfe.instrument a
 48                        INNER JOIN cfe.impairment b
 49                            ON a.id_instrument = b.id_instrument
 50                        LEFT JOIN cfe.instrument_attribute c
 51                            ON a.id_instrument = c.id_instrument
 52                                AND c.id_attribute = 'product'
 53                        INNER JOIN cfe.ext_yield t
 54                            ON ( a.id_currency = t.id_currency )
 55                                AND ( a.id_instrument_type LIKE t.id_instrument_type )
 56                                AND ( b.id_portfolio LIKE t.id_portfolio
 57                                        OR ( b.id_portfolio IS NULL
 58                                                AND t.id_portfolio = '%' ) )
 59                                AND ( c.attribute_value LIKE t.product_code
 60                                        OR ( c.attribute_value IS NULL
 61                                                AND t.product_code = '%' ) ) )
 62            SELECT /*+ PARALLEL */
 63                *
 64            FROM x x1
 65            WHERE x1.valid_date = ( SELECT max
 66                                    FROM x
 67                                    WHERE id_instrument = x1.id_instrument
 68                                        AND valid_date <= to_date ) ) s
 69        ON ( imp.id_instrument = s.id_instrument
 70                AND imp.measure = 'YIELD' )
 71WHEN MATCHED THEN
 72    UPDATE SET  imp.value = s.yield
 73;
 74
 75-- MERGE 3
 76MERGE INTO cfe.instrument_import_measure imp
 77    USING s
 78        ON ( imp.id_instrument = s.id_instrument
 79                AND imp.measure = 'YIELD_P'
 80                AND imp.id_instrument = s.id_instrument
 81                AND imp.measure = 'YIELD_P' )
 82WHEN MATCHED THEN
 83    UPDATE SET  imp.value = s.yield
 84;
 85
 86-- MERGE 4
 87MERGE INTO cfe.instrument_import_measure imp
 88    USING ( WITH x AS (
 89                    SELECT  a.id_instrument
 90                            , a.id_currency
 91                            , a.id_instrument_type
 92                            , b.id_portfolio
 93                            , c.attribute_value product_code
 94                            , t.valid_date
 95                            , t.yield
 96                    FROM cfe.instrument a
 97                        INNER JOIN cfe.impairment b
 98                            ON a.id_instrument = b.id_instrument
 99                        LEFT JOIN cfe.instrument_attribute c
100                            ON a.id_instrument = c.id_instrument
101                                AND c.id_attribute = 'product'
102                        INNER JOIN cfe.ext_yield t
103                            ON ( a.id_currency = t.id_currency )
104                                AND ( a.id_instrument_type LIKE t.id_instrument_type )
105                                AND ( b.id_portfolio LIKE t.id_portfolio
106                                        OR ( b.id_portfolio IS NULL
107                                                AND t.id_portfolio = '%' ) )
108                                AND ( c.attribute_value LIKE t.product_code
109                                        OR ( c.attribute_value IS NULL
110                                                AND t.product_code = '%' ) ) )
111            SELECT /*+ PARALLEL */
112                *
113            FROM x x1
114            WHERE x1.valid_date = ( SELECT max
115                                    FROM x
116                                    WHERE id_instrument = x1.id_instrument
117                                        AND valid_date <= to_date ) ) s
118        ON ( imp.id_instrument = s.id_instrument
119                AND imp.measure = 'YIELD_PP' )
120WHEN MATCHED THEN
121    UPDATE SET  imp.value = s.yield
122;
123
124-- MERGE DELETE WHERE
125MERGE INTO empl_current tar
126    USING ( SELECT  empno
127                    , ename
128                    , CASE
129                            WHEN leavedate <= sysdate
130                                THEN 'Y'
131                            ELSE 'N'
132                        END AS delete_flag
133            FROM empl ) src
134        ON ( tar.empno = src.empno )
135WHEN NOT MATCHED THEN
136    INSERT ( empno
137                , ename )
138    VALUES ( src.empno
139                , src.ename )
140WHEN MATCHED THEN
141    UPDATE SET  tar.ename = src.ename
142    WHERE   delete_flag = 'N'
143    DELETE WHERE    delete_flag = 'Y'
144;
145
146-- BOTH CLAUSES PRESENT
147MERGE INTO test1 a
148    USING all_objects
149        ON ( a.object_id = b.object_id )
150WHEN NOT MATCHED THEN
151    INSERT ( object_id
152                , status )
153    VALUES ( b.object_id
154                , b.status )
155WHEN MATCHED THEN
156    UPDATE SET  a.status = b.status
157    WHERE   b.status != 'VALID'
158;
159
160-- BOTH CLAUSES PRESENT 2
161MERGE INTO test1 a
162    USING all_objects
163        ON ( a.object_id = b.object_id )
164WHEN NOT MATCHED THEN
165    INSERT ( object_id
166                , status )
167    VALUES ( b.object_id
168                , b.status )
169    WHERE   b.status != 'VALID'
170WHEN MATCHED THEN
171    UPDATE SET  a.status = b.status
172    WHERE   b.status != 'VALID'
173;
174
175-- INSERT WITHOUT COLUMNS
176MERGE /*+ PARALLEL */ INTO cfe.tmp_eab a
177    USING ( SELECT /*+ PARALLEL DRIVING_SITE(C) */
178                c.*
179            FROM tbaadm.eab@finnacle c
180                INNER JOIN (    SELECT  acid
181                                        , eod_date
182                                FROM cfe.tmp_eab e
183                                WHERE end_eod_date = (  SELECT Max( eod_date )
184                                                        FROM cfe.tmp_eab
185                                                        WHERE acid = e.acid )
186                                    AND end_eod_date < '31-Dec-2099' ) d
187                    ON c.acid = d.acid
188                        AND c.eod_date >= d.eod_date ) b
189        ON ( a.acid = b.acid
190                AND a.eod_date = b.eod_date )
191WHEN MATCHED THEN
192    UPDATE SET  a.tran_date_bal = b.tran_date_bal
193                , a.tran_date_tot_tran = b.tran_date_tot_tran
194                , a.value_date_bal = b.value_date_bal
195                , a.value_date_tot_tran = b.value_date_tot_tran
196                , a.end_eod_date = b.end_eod_date
197                , a.lchg_user_id = b.lchg_user_id
198                , a.lchg_time = b.lchg_time
199                , a.rcre_user_id = b.rcre_user_id
200                , a.rcre_time = b.rcre_time
201                , a.ts_cnt = b.ts_cnt
202                , a.eab_crncy_code = b.eab_crncy_code
203                , a.bank_id = b.bank_id
204WHEN NOT MATCHED THEN
205    INSERT VALUES ( b.acid
206                    , b.eod_date
207                    , b.tran_date_bal
208                    , b.tran_date_tot_tran
209                    , b.value_date_bal
210                    , b.value_date_tot_tran
211                    , b.end_eod_date
212                    , b.lchg_user_id
213                    , b.lchg_time
214                    , b.rcre_user_id
215                    , b.rcre_time
216                    , b.ts_cnt
217                    , b.eab_crncy_code
218                    , b.bank_id )
219;
220
221-- MERGE WITH
222WITH wmachine AS (
223        SELECT DISTINCT
224            projcode
225            , plantcode
226            , buildingcode
227            , floorcode
228            , room
229        FROM tab_machinelocation
230        WHERE Trim( Room ) <> ''
231            AND Trim( Room ) <> '-' )
232MERGE INTO tab_roomlocation AS troom
233    USING wmachine
234        ON ( troom.projcode = wmachine.projcode
235                AND troom.plantcode = wmachine.plantcode
236                AND troom.buildingcode = wmachine.buildingcode
237                AND troom.floorcode = wmachine.floorcode
238                AND troom.room = wmachine.room )
239WHEN NOT MATCHED /* BY TARGET */ THEN
240    INSERT ( projcode
241                , plantcode
242                , buildingcode
243                , floorcode
244                , room )
245    VALUES ( wmachine.projcode
246                , wmachine.plantcode
247                , wmachine.buildingcode
248                , wmachine.floorcode
249                , wmachine.room )
250OUTPUT  Getdate() AS timeaction
251        , $action AS action
252        , inserted.projcode
253        , inserted.plantcode
254        , inserted.buildingcode
255        , inserted.floorcode
256        , inserted.room
257    INTO tab_mergeactions_roomlocation
258;

Insert

 1-- INSERT COUNTERPARTY COUNTERPARTY RELATIONSHIP
 2INSERT INTO risk.counterparty_counterparty
 3VALUES (    :id_counterparty_ref, :id_counterparty_beneficiary, :id_instrument_guarantee
 4            , :priority, :type, :description
 5            , :limit_amout, :id_currency, :end_date )
 6;
 7
 8-- INSERT RATIO COLLECTION RATIOS
 9INSERT INTO risk.counterparty_ratio
10VALUES ( ?, ?, ? )
11;
12
13-- INSERT TMP_CCF
14INSERT INTO risk.tmp_ccf (
15    "ID_INSTRUMENT"
16    , "TENOR"
17    , "STATUS"
18    , "OBSERVATION_DATE"
19    , "BALANCE"
20    , "LIMIT"
21    , "DR_BALANCE"
22    , "OPEN_LIMIT" )
23SELECT  '1000042339'       /* ID_INSTRUMENT */
24        , 0                /* TENOR */
25        , 'DEFAULT'        /* STATUS */
26        , {d '2020-02-27'} /* OBSERVATION_DATE */
27        , - 142574953.65   /* BALANCE */
28        , 300000000        /* LIMIT */
29        , - 142574953.65   /* DR_BALANCE */
30        , 157425046.35     /* OPEN_LIMIT */
31FROM dual
32;
33
34-- APPEND ATTRIBUTE VALUE REF
35INSERT INTO cfe.attribute_value_ref
36SELECT  cfe.id_attribute_value_ref.nextval
37        , attribute_value
38FROM (  SELECT DISTINCT
39            a.attribute_value
40        FROM cfe.instrument_attribute a
41            LEFT JOIN cfe.attribute_value_ref b
42                ON a.attribute_value = b.attribute_value
43        WHERE b.attribute_value IS NULL ) a
44;
45
46-- INSERT WITH
47WITH scope AS (
48        SELECT *
49        FROM cfe.accounting_scope
50        WHERE id_status = 'C'
51            AND id_accounting_scope_code = :SCOPE )
52INSERT INTO cfe.accounting_scope a
53SELECT *
54FROM scope
55;

Update

  1-- UPDATE COUNTERPARTY
  2UPDATE risk.counterparty
  3SET id_counterparty = :id_counterparty
  4    , label = :label
  5    , description = :description
  6    , id_counterparty_group_type = :id_counterparty_group_type
  7    , id_counterparty_type = :id_counterparty_type
  8    , id_counterparty_sub_type = :id_counterparty_sub_type
  9    , id_country_group = :id_country_group
 10    , id_country = :id_country
 11    , id_country_state = :id_country_state
 12    , id_district = :id_district
 13    , id_city = :id_city
 14    , id_industrial_sector = :id_industrial_sector
 15    , id_industrial_sub_sector = :id_industrial_sub_sector
 16    , block_auto_update_flag = :block_auto_update_flag
 17    , id_user_editor = :id_user_editor
 18    , id_organization_unit = :id_organization_unit
 19    , id_status = :id_status
 20    , update_timestamp = current_timestamp
 21WHERE id_counterparty_ref = :id_counterparty_ref
 22;
 23
 24-- UPDATE COLLATERAL_TYPE
 25UPDATE common.collateral_type
 26SET hair_cut = least
 27WHERE id_collateral_type_ref IN (   SELECT id_collateral_type_ref
 28                                    FROM common.collateral_type a
 29                                    WHERE id_status IN (    'C', 'H', 'C'
 30                                                            , 'H', 'C', 'H'
 31                                                            , 'C', 'H' )
 32                                        AND id_collateral_type_ref = (  SELECT Max( id_collateral_type_ref )
 33                                                                        FROM common.collateral_type
 34                                                                        WHERE id_status IN ( 'C', 'H' )
 35                                                                            AND id_collateral_type = a.id_collateral_type ) )
 36;
 37
 38-- UPDATE COUNTERPARTY_INSTRUMENT
 39UPDATE risk.counterparty_instrument a1
 40SET (   priority
 41        , type
 42        , description
 43        , limit_amout
 44        , id_currency
 45        , end_date ) = (    SELECT  a.priority
 46                                    , a.type
 47                                    , a.description
 48                                    , a.limit_amout
 49                                    , a.id_currency
 50                                    , a.end_date
 51                            FROM risk.imp_counterparty_instrument a
 52                                INNER JOIN risk.counterparty b
 53                                    ON a.id_counterparty = b.id_counterparty
 54                                        AND b.id_status = 'C'
 55                                INNER JOIN risk.instrument c
 56                                    ON a.id_instrument_beneficiary = c.id_instrument
 57                                        AND c.id_status = 'C'
 58                                INNER JOIN risk.counterparty_instrument e
 59                                    ON b.id_counterparty_ref = e.id_counterparty_ref
 60                                        AND e.id_instrument_beneficiary = a.id_instrument_beneficiary
 61                                        AND e.id_instrument_guarantee = a.id_instrument_guarantee
 62                            WHERE e.id_counterparty_ref = a1.id_counterparty_ref
 63                                AND e.id_instrument_beneficiary = a1.id_instrument_beneficiary
 64                                AND e.id_instrument_guarantee = a1.id_instrument_guarantee )
 65WHERE EXISTS (  SELECT  a.priority
 66                        , a.type
 67                        , a.description
 68                        , a.limit_amout
 69                        , a.id_currency
 70                        , a.end_date
 71                FROM risk.imp_counterparty_instrument a
 72                    INNER JOIN risk.counterparty b
 73                        ON a.id_counterparty = b.id_counterparty
 74                            AND b.id_status = 'C'
 75                    INNER JOIN risk.instrument c
 76                        ON a.id_instrument_beneficiary = c.id_instrument
 77                            AND c.id_status = 'C'
 78                    INNER JOIN risk.counterparty_instrument e
 79                        ON b.id_counterparty_ref = e.id_counterparty_ref
 80                            AND e.id_instrument_beneficiary = a.id_instrument_beneficiary
 81                            AND e.id_instrument_guarantee = a.id_instrument_guarantee
 82                WHERE e.id_counterparty_ref = a1.id_counterparty_ref
 83                    AND e.id_instrument_beneficiary = a1.id_instrument_beneficiary
 84                    AND e.id_instrument_guarantee = a1.id_instrument_guarantee )
 85;
 86
 87-- UPDATE SETS ISSUE 1316
 88UPDATE prpjpaymentbill b
 89SET (   b.packagecode
 90        , b.packageremark
 91        , b.agentcode ) = ( SELECT  p.payrefreason
 92                                    , p.classcode
 93                                    , p.riskcode
 94                            FROM prpjcommbill p
 95                            WHERE p.policertiid = 'SDDH200937010330006366' )   /* this is supposed to be UpdateSet 1 */
 96    , b.payrefnotype = '05'                                                    /* this is supposed to be UpdateSet 2 */
 97    , b.packageunit = '4101170402'                                             /* this is supposed to be UpdateSet 3 */
 98WHERE b.payrefno = 'B370202091026000005'
 99;
100
101-- UPDATE START JOINS
102UPDATE sc_borrower b
103    INNER JOIN sc_credit_apply a
104        ON a.borrower_id = b.id
105SET b.name = '0.7505105896846266'
106    , a.credit_line = a.credit_line + 1
107WHERE b.id = 3
108;
109
110-- UPDATE JOINS
111UPDATE table1
112SET columna = 5
113FROM table1
114    LEFT JOIN table2
115        ON col1 = col2
116;
117
118-- UPDATE WITH
119WITH s AS (
120        SELECT  a.priority
121                , a.type
122                , a.description
123                , a.limit_amout
124                , a.id_currency
125                , a.end_date
126        FROM risk.imp_counterparty_instrument a
127            INNER JOIN risk.counterparty b
128                ON a.id_counterparty = b.id_counterparty
129                    AND b.id_status = 'C'
130            INNER JOIN risk.instrument c
131                ON a.id_instrument_beneficiary = c.id_instrument
132                    AND c.id_status = 'C'
133            INNER JOIN risk.counterparty_instrument e
134                ON b.id_counterparty_ref = e.id_counterparty_ref
135                    AND e.id_instrument_beneficiary = a.id_instrument_beneficiary
136                    AND e.id_instrument_guarantee = a.id_instrument_guarantee
137        WHERE e.id_counterparty_ref = a1.id_counterparty_ref
138            AND e.id_instrument_beneficiary = a1.id_instrument_beneficiary
139            AND e.id_instrument_guarantee = a1.id_instrument_guarantee )
140UPDATE risk.counterparty_instrument a1
141SET (   priority
142        , type
143        , description
144        , limit_amout
145        , id_currency
146        , end_date ) = (    SELECT *
147                            FROM s )
148WHERE EXISTS (  SELECT  a.priority
149                        , a.type
150                        , a.description
151                        , a.limit_amout
152                        , a.id_currency
153                        , a.end_date
154                FROM risk.imp_counterparty_instrument a
155                    INNER JOIN risk.counterparty b
156                        ON a.id_counterparty = b.id_counterparty
157                            AND b.id_status = 'C'
158                    INNER JOIN risk.instrument c
159                        ON a.id_instrument_beneficiary = c.id_instrument
160                            AND c.id_status = 'C'
161                    INNER JOIN risk.counterparty_instrument e
162                        ON b.id_counterparty_ref = e.id_counterparty_ref
163                            AND e.id_instrument_beneficiary = a.id_instrument_beneficiary
164                            AND e.id_instrument_guarantee = a.id_instrument_guarantee
165                WHERE e.id_counterparty_ref = a1.id_counterparty_ref
166                    AND e.id_instrument_beneficiary = a1.id_instrument_beneficiary
167                    AND e.id_instrument_guarantee = a1.id_instrument_guarantee )
168;

Comments

 1------------------------------------------------------------------------------------------------------------------------
 2-- CONFIGURATION
 3------------------------------------------------------------------------------------------------------------------------
 4
 5-- UPDATE CALENDAR
 6UPDATE cfe.calendar
 7SET year_offset = ?            /* year offset */
 8    , settlement_shift = ?     /* settlement shift */
 9    , friday_is_holiday = ?    /* friday is a holiday */
10    , saturday_is_holiday = ?  /* saturday is a holiday */
11    , sunday_is_holiday = ?    /* sunday is a holiday */
12WHERE id_calendar = ?
13;
14
15-- BOTH CLAUSES PRESENT 'with a string' AND "a field"
16MERGE /*+ PARALLEL */ INTO test1 /*the target table*/ a
17    USING all_objects      /*the source table*/
18        ON ( /*joins in()!;*/ a.object_id = b.object_id )
19-- INSERT CLAUSE;
20WHEN /*comments between keywords!;*/ NOT MATCHED THEN
21    INSERT ( object_id     /*ID Column*/
22                , status   /*Status Column*/ )
23    VALUES ( b.object_id
24                , b.status )
25/* UPDATE CLAUSE
26WITH A WHERE CONDITION */ 
27WHEN MATCHED THEN          /* Lets rock */
28    UPDATE SET  a.status = '/*this is no comment!*/ and -- this ain''t either;'
29    WHERE   b."--status;" != 'VALID'
30;

MS Sql Server

 1-- BRACKETS 1
 2SELECT columnname
 3FROM [server-name\\server-instance]..schemaname.tablename
 4;
 5
 6-- BRACKETS 2
 7SELECT columnname
 8FROM [server-name\\server-instance]..[schemaName].[table Name]
 9;
10
11-- BRACKETS 3
12SELECT columnname
13FROM [server-name\\server-instance]..[schemaName].[table-Name]
14;
15
16-- BRACKETS 4
17SELECT columnname
18FROM [schemaName].[tableName]
19;
20
21-- BRACKETS 5
22SELECT columnname
23FROM schemaname.[tableName]
24;
25
26-- BRACKETS 6
27SELECT columnname
28FROM [schemaName].tablename
29;
30
31-- READ INSTRUMENT TRANSACTIONS WITH COLLATERAL ONLY
32SELECT a.*
33FROM [cfe].[TRANSACTION] a
34    INNER JOIN cfe.instrument b
35        ON a.id_instrument = b.id_instrument
36WHERE a.id_instrument >= ?
37    AND a.id_instrument <= ?
38    AND EXISTS (    SELECT 1
39                    FROM cfe.instrument_ref b
40                        INNER JOIN cfe.instrument_collateral_hst c
41                            ON b.id_instrument_ref = c.id_instrument_ref
42                    WHERE b.id_instrument = a.id_instrument )
43;
 1-- DELETE INSTRUMENT ATTRIBUTE HST AFTER VALUE_DATE_P
 2DELETE a FROM cfe.instrument_attribute_hst2 a
 3    INNER JOIN (    SELECT  value_date
 4                            , posting_date
 5                    FROM cfe.execution
 6                    WHERE posting_date > (  SELECT Max( posting_date )
 7                                            FROM cfe.execution
 8                                            WHERE id_status = 'R'
 9                                                AND value_date <= :value_date_p )
10                        OR (    SELECT Max( posting_date )
11                                FROM cfe.execution
12                                WHERE id_status = 'R'
13                                    AND value_date <= :value_date_p ) IS NULL ) b
14        ON a.value_date = b.value_date
15            AND b.posting_date = b.posting_date
16;
17
18-- READ INSTRUMENT TRANSACTIONS WITH COLLATERAL ONLY1
19SELECT a.*
20FROM [cfe].[TRANSACTION] a
21    INNER JOIN cfe.instrument b
22        ON a.id_instrument = b.id_instrument
23WHERE a.id_instrument >= ?
24    AND a.id_instrument <= ?
25    AND EXISTS (    SELECT 1
26                    FROM cfe.instrument_ref b
27                        INNER JOIN cfe.instrument_collateral_hst c
28                            ON b.id_instrument_ref = c.id_instrument_ref
29                    WHERE b.id_instrument = a.id_instrument )
30;

Formatting Options

 1-- 1 UPDATE CALENDAR
 2-- @JSQLFormatter(indentWidth=8, keywordSpelling=UPPER, functionSpelling=CAMEL, objectSpelling=LOWER, separation=BEFORE)
 3UPDATE cfe.calendar
 4SET     year_offset = ?                    /* year offset */
 5        , settlement_shift = To_Char( ? )  /* settlement shift */
 6        , friday_is_holiday = ?            /* friday is a holiday */
 7        , saturday_is_holiday = ?          /* saturday is a holiday */
 8        , sunday_is_holiday = ?            /* sunday is a holiday */
 9WHERE id_calendar = ?
10;
11
12
13-- 2 UPDATE CALENDAR
14-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
15update CFE.CALENDAR
16set YEAR_OFFSET = ?                    /* year offset */,
17    SETTLEMENT_SHIFT = to_char( ? )    /* settlement shift */,
18    FRIDAY_IS_HOLIDAY = ?              /* friday is a holiday */,
19    SATURDAY_IS_HOLIDAY = ?            /* saturday is a holiday */,
20    SUNDAY_IS_HOLIDAY = ?              /* sunday is a holiday */
21where ID_CALENDAR = ?
22;
23
24
25-- 3 MERGE DELETE WHERE
26-- @JSQLFormatter(indentWidth=2, keywordSpelling=LOWER, functionSpelling=KEEP, objectSpelling=UPPER, separation=AFTER)
27merge into EMPL_CURRENT TAR
28  using ( select  EMPNO,
29                  ENAME,
30                  case
31                      when LEAVEDATE <= SYSDATE
32                        then 'Y'
33                      else 'N'
34                    end as DELETE_FLAG
35          from EMPL ) SRC
36    on ( TAR.EMPNO = SRC.EMPNO )
37when not matched then
38  insert ( EMPNO,
39            ENAME )
40  values ( SRC.EMPNO,
41            SRC.ENAME )
42when matched then
43  update set  TAR.ENAME = SRC.ENAME
44  where DELETE_FLAG = 'N'
45  delete where  DELETE_FLAG = 'Y'
46;