文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> 使用Migration Tool> SQL Formatter

SQL Formatter

分享
更新时间: 2019/08/09 11:20

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

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

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

输入: SQL FORMATTER

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
;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区