更新时间：2023-03-17 GMT+08:00
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
