Help Center> Data Warehouse Service (DWS)> Tool Guide (Kuala Lumpur Region)> DSC: SQL Syntax Migration Tool> Using DSC> SQL Formatter
Updated on 2023-03-17 GMT+08:00
View PDF

SQL Formatter

SQL Formatter improves the readability of SQL statements. It formats SQL statements by adding/deleting rows and indenting context, and is used to format migrated output files. It can also be used for importing files.

Use the formattedSourceRequired parameter to enable/disable the SQL formatter for the source SQL files. If this parameter is set to true, the copy of the input file is formatted and saved to the Output path\formattedSource directory.

SQL Formatter also supports Teradata SQL migration and Oracle SQL migration. SQL scripts in Teradata Perl file migration are also formatted. The Oracle (Beta) tool does not support SQL Formatter.

Input: SQL FORMATTER

1
 
select                 p1.parti_encode                  ,p1.accting_type_cd                 ,p1.prod_code                ,p1.cust_type_cd                 ,p1.accting_amt_type_cd                 ,p1.accting_num_1                 ,p1.accting_num_2                  ,p1.accting_num_3                  ,p1.accting_num_4                 ,p1.accting_num_5                ,p1.accting_num_6                 ,p1.start_dt                  ,p1.pre_effect_debit_gl_num                  ,p1.pre_effect_crdt_gl_num                  ,p1.after_effect_debit_gl_num                  ,p1.after_effect_crdt_gl_num                ,coalesce( p2.start_dt ,cast( '30001231' as date format 'yyyymmdd' ) )                  ,p1.accting_term                  ,p1.etl_job             from                 (                     select                             rank (                                 start_dt asc                             ) as start_dt_id                             ,parti_encode                              ,accting_type_cd                              ,prod_code                             ,cust_type_cd                              ,accting_amt_type_cd                              ,accting_num_1                              ,accting_num_2                              ,accting_num_3                             ,accting_num_4                            ,accting_num_5                            ,accting_num_6                            ,start_dt                             ,pre_effect_debit_gl_num                             ,pre_effect_crdt_gl_num                              ,after_effect_debit_gl_num                              ,after_effect_crdt_gl_num                              ,accting_term                              ,etl_job                          from                             ccting_subj_para_h_mf0_a_cur_i                  ) p1 left join (                     select                             rank (                                 start_dt asc                             ) - 1 as start_dt_id                             ,parti_encode                             ,accting_type_cd                            ,prod_code                              ,cust_type_cd                             ,accting_amt_type_cd                             ,accting_num_1                             ,accting_num_2                             ,accting_num_3                           ,accting_num_4                              ,accting_num_5                             ,accting_num_6                              ,start_dt                           ,accting_term                          from                             ccting_subj_para_h_mf0_a_cur_i                 ) p2                     on p1.start_dt_id = p2.start_dt_id                 and p1.parti_encode = p2.parti_encode                 and p1.accting_type_cd = p2.accting_type_cd                 and p1.prod_code = p2.prod_code                 and p1.cust_type_cd = p2.cust_type_cd                 and p1.accting_amt_type_cd = p2.accting_amt_type_cd                 and p1.accting_num_1 = p2.accting_num_1                 and p1.accting_num_2 = p2.accting_num_2                 and p1.accting_num_3 = p2.accting_num_3                 and p1.accting_num_4 = p2.accting_num_4                 and p1.accting_num_5 = p2.accting_num_5                 and p1.accting_num_6 = p2.accting_num_6                 and p1.accting_term = p2.accting_term ;

Output

 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
 
SELECT
          p1.parti_encode                      ,p1.accting_type_cd         
          ,p1.prod_code              
          ,p1.cust_type_cd                     ,p1.accting_amt_type_cd     
          ,p1.accting_num_1          
          ,p1.accting_num_2           
          ,p1.accting_num_3           
          ,p1.accting_num_4           
          ,p1.accting_num_5           
          ,p1.accting_num_6           
          ,p1.start_dt                
          ,p1.pre_effect_debit_gl_num 
          ,p1.pre_effect_crdt_gl_num  
          ,p1.after_effect_debit_gl_num  
          ,p1.after_effect_crdt_gl_num   
          ,COALESCE( p2.start_dt ,CAST( '30001231' AS DATE ) )   
          ,p1.accting_term                    ,p1.etl_job                
     FROM
          (
               SELECT
                         rank (
                         ) over( ORDER BY start_dt ASC ) AS start_dt_id
                         ,parti_encode            
                         ,accting_type_cd         
                         ,prod_code               
                         ,cust_type_cd            
                         ,accting_amt_type_cd     
                         ,accting_num_1           
                         ,accting_num_2           
                         ,accting_num_3           
                         ,accting_num_4           
                         ,accting_num_5          
                         ,accting_num_6           
                         ,start_dt                                         ,pre_effect_debit_gl_num                          ,pre_effect_crdt_gl_num   
                         ,after_effect_debit_gl_num 
                         ,after_effect_crdt_gl_num  
                         ,accting_term                                       ,etl_job                                       FROM
                         ccting_subj_para_h_mf0_a_cur_i
          ) p1 LEFT JOIN (
               SELECT
                         rank (
                         ) over( ORDER BY start_dt ASC ) - 1 AS start_dt_id
                         ,parti_encode           
                         ,accting_type_cd        
                         ,prod_code              
                         ,cust_type_cd           
                         ,accting_amt_type_cd    
                         ,accting_num_1         
                         ,accting_num_2          
                         ,accting_num_3          
                         ,accting_num_4          
                         ,accting_num_5          
                         ,accting_num_6         
                         ,start_dt               
                         ,accting_term           
                    FROM
                         ccting_subj_para_h_mf0_a_cur_i
          ) p2
               ON p1.start_dt_id = p2.start_dt_id
          AND p1.parti_encode = p2.parti_encode
          AND p1.accting_type_cd = p2.accting_type_cd
          AND p1.prod_code = p2.prod_code
          AND p1.cust_type_cd = p2.cust_type_cd
          AND p1.accting_amt_type_cd = p2.accting_amt_type_cd
          AND p1.accting_num_1 = p2.accting_num_1
          AND p1.accting_num_2 = p2.accting_num_2
          AND p1.accting_num_3 = p2.accting_num_3
          AND p1.accting_num_4 = p2.accting_num_4
          AND p1.accting_num_5 = p2.accting_num_5
          AND p1.accting_num_6 = p2.accting_num_6
          AND p1.accting_term = p2.accting_term
;
Parent topic: Using DSC