Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> Using DSC> SQL Formatter
View PDF

SQL Formatter

The SQL formatter is provided to improve the readability of the SQL. It formats the SQL statements by adding/removing lines and contextual level indentation. The SQL Formatter formats migration outputs by default. However, it can also formats migraton inputs.

Use the formattedSourceRequired parameter to enable/disable the SQL formatter for the source SQL files. If it is set to true, a copy of the input files is formatted and saved to {outputpath}\formattedSource folder.

The SQL formatter is supported for Teradata SQL Migration, and the Oracle SQL Migration. SQL scripts in Teradata Perl file migration are also formatted.

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