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

 工具下载链接：

https://pan.baidu.com/s/11jwAWkVM1zXN1Iwv3MJaDg
提取码：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_PRIORITY
 from  (
   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')
) TTAB0
   where 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_dependency
	where etl_system = 'CTL'
	and etl_job = 'CTL_END'
	and enbale =1
union all
	select b.etl_job,b.dependency_job,level+1
	from rectbl a
	inner join etl_job_dependency b
	on a.dependency_job = b.etl_job
	and 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_dependency
	where etl_system = 'CTL'
	and etl_job = 'CTL_END'
	and enbale =1
union all
	select b.etl_job,b.dependency_job,level+1
	from rectbl a
	inner join etl_job_dependency b
	on a.dependency_job = b.etl_job
	and 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.level
 from  (
   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
) TTAB16
   where 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';

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

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

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