TD迁移 teradata qualify函数语法转换工具

语法转换工具"/>

TD迁移 teradata qualify函数语法转换工具

 工具下载链接：

提取码：qftf

转换前：

SELECT 	ETL_SYSTEM,ETL_JOB,JOB_TYPE,LAST_TXDATE,LAST_STARTTIME,LAST_ENDTIME,JOB_PRIORITY
FROM ETL_JOB 
WHERE T1.ETL_JOB IN(SELECT ETL_JOB FROM PETL.ETL_JOB_DEPENDECNY WHERE DEPENDENCY_JOB = 'DEPENDENCY_JOB' )
AND T1.ETL_JOB IN (SELECT DEPENDENCY_JOB FROM PETL.ETL_JOB_DEPENDECNY WHERE DEPENDENCY_JOB = 'ETL_JOB')
QUALIFY ROW_NUMBER() OVER(PARTITION BY ETL_SYSTEM ORDER BY JOB_PRIORITY DESC) = 1 
ORDER BY 1 DESC;

转换后：

SELECT 	ETL_SYSTEM,ETL_JOB,JOB_TYPE,LAST_TXDATE,LAST_STARTTIME,LAST_ENDTIME,JOB_PRIORITYfrom  (SELECT 	ETL_SYSTEM,ETL_JOB,JOB_TYPE,LAST_TXDATE,LAST_STARTTIME,LAST_ENDTIME,JOB_PRIORITY, row_number() over(partition by etl_system order by job_priority desc) as RowNumBer
FROM ETL_JOB 
WHERE T1.ETL_JOB IN(SELECT ETL_JOB FROM PETL.ETL_JOB_DEPENDECNY WHERE DEPENDENCY_JOB = 'DEPENDENCY_JOB' )
AND T1.ETL_JOB IN (SELECT DEPENDENCY_JOB FROM PETL.ETL_JOB_DEPENDECNY WHERE DEPENDENCY_JOB = 'ETL_JOB')
) TTAB0where RowNumBer = 1 
ORDER BY 1 DESC;

递归转换前：

with recursive as rectbl (etl_job,dependency_job,level) as (select etl_job,dependency_job，2(integer)from etl_job_dependencywhere etl_system = 'CTL'and etl_job = 'CTL_END'and enbale =1
union allselect b.etl_job,b.dependency_job,level+1from rectbl ainner join etl_job_dependency bon a.dependency_job = b.etl_joband b.enbale = 1)
select  t3.etl_system d_syatem,t1.dependency_job d_job,t3.jobtype,t3.last_jobstatus,t3.last_starttime,t3.last_endtime,t3.last_txdate,t3.job_priority,max(t1.level)
from rectbl t1
inner join etl_job t3
on t1.dependency_job = t3.etl_job
qualify row_number() over (partition by t1.dependency_job order by level desc) = 1
union all
select etl_system,etl_job,jobtype,last_jobstatus,last_starttime,last_endtime,last_txdate,job_priority,1
from etl_job
where etl_system = 'CTL'
and etl_job = 'CTL_END';

递归转换后：

with recursive as rectbl (etl_job,dependency_job,level) as (select etl_job,dependency_job，2(integer)from etl_job_dependencywhere etl_system = 'CTL'and etl_job = 'CTL_END'and enbale =1
union allselect b.etl_job,b.dependency_job,level+1from rectbl ainner join etl_job_dependency bon a.dependency_job = b.etl_joband b.enbale = 1)
select  t3.etl_system d_syatem,t1.dependency_job d_job,t3.jobtype,t3.last_jobstatus,t3.last_starttime,t3.last_endtime,t3.last_txdate,t3.job_priority,t1.levelfrom  (select  t3.etl_system d_syatem,t1.dependency_job d_job,t3.jobtype,t3.last_jobstatus,t3.last_starttime,t3.last_endtime,t3.last_txdate,t3.job_priority,t1.level, row_number() over (partition by t1.dependency_job order by level desc) as RowNumBer
from rectbl t1
inner join etl_job t3
on t1.dependency_job = t3.etl_job
) TTAB16where RowNumBer = 1 
union all
select etl_system,etl_job,jobtype,last_jobstatus,last_starttime,last_endtime,last_txdate,job_priority,1
from etl_job
where etl_system = 'CTL'
and etl_job = 'CTL_END';

第一种初步正常转换已经可以完成，但是在这里出现了转换后使用别名的问题，还需要手工做一下简单的修改。

大家有好的处理方案，欢迎一起讨论讨论。

需要处理别名问题，可以联系制作者讨论一下。