文档首页> 数据仓库服务 GaussDB(DWS)> 工具指南（阿布扎比区域）> DSC SQL语法迁移工具> 使用DSC> SQL Formatter
更新时间：2023-03-17 GMT+08:00
查看PDF

SQL Formatter

SQL Formatter用于提高SQL的可读性。它通过添加/删除行和上下文缩进来对SQL语句进行格式化。SQL Formatter通常用于对迁移后的输出文件进行格式化，也可用于输入文件。

可以使用formattedSourceRequired参数来决定是否对源SQL文件使用SQL Formatter。若该参数设为true，则对输入文件的副本进行格式化并保存到{输出路径}\formattedSource文件夹。

SQL Formatter还支持Teradata SQL和Oracle SQL迁移。Teradata Perl文件迁移中的SQL脚本也是格式化的。Oracle（Beta）工具不支持SQL Formatter。

输入: 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 ;

输出

 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
;
父主题： 使用DSC