|
| 1 | +suite("load") { |
| 2 | + def tables = |
| 3 | + [ |
| 4 | + "STG_CUX_FND_COA_SEG3_V" |
| 5 | + ] |
| 6 | + for (String table in tables) { |
| 7 | + sql """DROP TABLE IF EXISTS ${table}""" |
| 8 | + sql new File("""${context.file.parent}/ddl/${table}.sql""").text |
| 9 | + } |
| 10 | + |
| 11 | + sql """ |
| 12 | + insert into STG_CUX_FND_COA_SEG3_V values ('a', 'b', 'c','d','e','f','g','h', 'i', 'j'); |
| 13 | + """ |
| 14 | + |
| 15 | + // 创建视图 |
| 16 | + sql """ |
| 17 | + create view STG_CUX_FND_COA_SEG3_V_MV_T as |
| 18 | +SELECT `scv`.`flex_value` AS `SEGMENT3`, |
| 19 | + `scv`.`flex_desc` AS `SEGMENT3_DESC`, |
| 20 | + `scv`.`flex_value_lev1` AS `SEGMENT3_LEV1`, |
| 21 | + `scv`.`flex_desc_lev1` AS `SEGMENT3_LEV1_DESC`, |
| 22 | + `scv`.`flex_value_lev2` AS `SEGMENT3_LEV2`, |
| 23 | + `scv`.`flex_desc_lev2` AS `SEGMENT3_LEV2_DESC`, |
| 24 | + `scv`.`flex_value_lev3` AS `SEGMENT3_LEV3`, |
| 25 | + `scv`.`flex_desc_lev3` AS `SEGMENT3_LEV3_DESC` |
| 26 | +FROM (SELECT `sccv`.`flex_value_set_name`, |
| 27 | + `sccv`.`flex_value`, |
| 28 | + `sccv`.`flex_desc`, |
| 29 | + `sccv`.`flex_value_lev1`, |
| 30 | + `sccv`.`flex_desc_lev1`, |
| 31 | + `sccv`.`flex_value_lev2`, |
| 32 | + `sccv`.`flex_desc_lev2`, |
| 33 | + `sccv`.`flex_value_lev3`, |
| 34 | + `sccv`.`flex_desc_lev3`, |
| 35 | + `sccv`.`last_update_date`, |
| 36 | + ROW_NUMBER() OVER (PARTITION BY `sccv`.`flex_value` ORDER BY `sccv`.`last_update_date` DESC) AS `GROUP_IDX` |
| 37 | + FROM `STG_CUX_FND_COA_SEG3_V` `sccv`) `scv` |
| 38 | +WHERE `scv`.`group_idx` = 1; |
| 39 | + """ |
| 40 | + |
| 41 | +} |
0 commit comments