查询迁移操作符
本节主要介绍Teradata查询迁移操作符的迁移语法。迁移语法决定了关键字/特性的迁移方式。
具体详见以下节点内容:
QUALIFY
通常,QUALIFY子句和CSUM()、MDIFF()、ROW_NUMBER()、RANK()等分析函数(窗口函数)一同使用。子查询中会包含QUALIFY子句指定的窗口函数。迁移工具支持QUALIFY使用MDIFF()、RANK()和ROW_NUMBER()函数。QUALIFY是Teradata扩展项,不是标准ANSI语法。QUALIFY在WHERE和GROUP BY子句后执行,必须单独成行。

只有当SELECT语句显式包含列名和/或表达式时,DSC才允许在ORDER BY子句中指定该列名和/或表达式。
输入:QUALIFY
1 2 3 4 5 6 | SELECT
CUSTOMER_ID
,CUSTOMER_NAME
FROM
CUSTOMER_T QUALIFY row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) = 1
;
|
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT
CUSTOMER_ID
,CUSTOMER_NAME
FROM
(
SELECT
CUSTOMER_ID
,CUSTOMER_NAME
,row_number( ) Over( partition BY CUSTOMER_ID ORDER BY POSTAL_CODE DESC ) AS ROW_NUM1
FROM
CUSTOMER_T
) Q1
WHERE
Q1.ROW_NUM1 = 1
;
|
输入:QUALIFY,使用MDIFF和RANK
1 2 3 4 5 6 7 8 9 | SELECT
material_name
,unit_of_measure * standard_cost AS tot_cost
FROM
raw_material_t m LEFT JOIN supplies_t s
ON s.material_id = m.material_id
QUALIFY rank ( ) over( ORDER BY tot_cost DESC ) IN '5'
OR mdiff( tot_cost ,3 ,material_name ) IS NULL
;
|
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT
material_name
,tot_cost
FROM
(
SELECT
material_name
,unit_of_measure * standard_cost AS tot_cost
,rank ( ) over( ORDER BY unit_of_measure * standard_cost DESC ) AS ROW_NUM1
,unit_of_measure * standard_cost - (LAG( unit_of_measure * standard_cost ,3 ,NULL ) over( ORDER BY material_name )) AS ROW_NUM2
FROM
raw_material_t m LEFT JOIN supplies_t s
ON s.material_id = m.material_id
) Q1
WHERE
Q1.ROW_NUM1 = '5'
OR Q1.ROW_NUM2 IS NULL
;
|
输入:QUALIFY,使用ORDER BY且ORDER BY中包含不存在于SELECT列表的列
1 2 3 4 5 6 | SELECT Postal_Code
FROM db_pvfc9_std.Customer_t t1
GROUP BY Customer_Name ,Postal_Code
QUALIFY ---comments
( Rank ( CHAR(Customer_Address) DESC ) ) = 1
ORDER BY t1.Customer_Name;
|
输出
1 2 3 4 5 6 7 8 | SELECT Postal_Code FROM
( SELECT Customer_Name, Postal_Code
, Rank () over( PARTITION BY Customer_Name, Postal_Code ORDER BY LENGTH(Customer_Address) DESC ) AS Rank_col
FROM db_pvfc9_std.Customer_t t1
) Q1
WHERE /*comments*/
Q1.Rank_col = 1
ORDER BY Q1.Customer_Name;
|
输入:QUALIFY,使用列别名且不应在SELECT列表中再次添加相应的列表达式
1 2 3 4 5 | SELECT material_name, unit_of_measure * standard_cost as tot_cost,
RANK() over(order by tot_cost desc) vendor_cnt
FROM raw_material_t m left join supplies_t s
ON s.material_id = m.material_id
QUALIFY vendor_cnt < 5 or MDIFF(tot_cost, 3, material_name) IS NULL;
|
输出
1 2 3 4 5 6 7 8 9 10 | SELECT material_name, tot_cost, vendor_cnt
FROM ( SELECT material_name
, unit_of_measure * standard_cost AS tot_cost
, rank () over (ORDER BY tot_cost DESC) vendor_cnt
, tot_cost - ( LAG(tot_cost ,3 ,NULL) over (ORDER BY material_name) ) AS anltfn
FROM raw_material_t m LEFT JOIN supplies_t s
ON s.material_id = m.material_id
) Q1
WHERE Q1.vendor_cnt < 5 OR Q1.anltfn IS NULL
;
|
TITLE和QUALIFY
输入
REPLACE VIEW ${STG_VIEW}.LP06_BMCLIINFP${v_Table_Suffix_Inc} ( CLICLINBR , CLICHNNAM , CLICHNSHO , CLICLIMNE , CLIBNKCOD ) AS LOCKING ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} FOR ACCESS SELECT CLICLINBR (title ' VARCHAR(20)') , CLICHNNAM (title ' VARCHAR(200)') , CLICHNSHO (title ' VARCHAR(20)') , CLICLIMNE (title ' VARCHAR(10)') , CLIBNKCOD (title ' VARCHAR(11)') FROM ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} s1 QUALIFY ROW_NUMBER() OVER(PARTITION BY CLICLINBR ORDER BY CLICLINBR ) = 1 ;
输出
CREATE OR REPLACE VIEW ${STG_VIEW}.LP06_BMCLIINFP${v_Table_Suffix_Inc} ( CLICLINBR , CLICHNNAM , CLICHNSHO , CLICLIMNE , CLIBNKCOD ) AS /* LOCKING ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} FOR ACCESS */ SELECT CLICLINBR , CLICHNNAM , CLICHNSHO , CLICLIMNE , CLIBNKCOD FROM ( SELECT CLICLINBR /* (title ' VARCHAR(20)') */ , CLICHNNAM /* (title ' VARCHAR(200)') */ , CLICHNSHO /* (title ' VARCHAR(20)') */ , CLICLIMNE /* (title ' VARCHAR(10)') */ , CLIBNKCOD /* (title ' VARCHAR(11)') */ , ROW_NUMBER() OVER(PARTITION BY CLICLINBR ORDER BY CLICLINBR ) AS ROWNUM1 FROM ${STG_DATA}.LP06_BMCLIINFP${v_Table_Suffix_Inc} s1 ) Q1 WHERE Q1.ROWNUM1 = 1 ;
ALIAS
所有数据库都支持ALIAS。在Teradata中,定义ALIAS的语句允许其SELECT和WHERE子句引用ALIAS。但是目标数据库的SELECT和WHERE语句不支持ALIAS,因此MT将其迁移为实际的字段名称替换。

比较操作符LT、LE、GT、GE、EQ和NE不得用作表别名或列别名。
工具支持列的ALIAS名称。如果ALIAS名称与列名称相同,则仅为该列而非表中其他列指定ALIAS。在以下示例中,DATA_DT列名称与DATA_DT别名之间存在冲突,工具不支持。
1 | SELECT DATA_DT,DATA_INT AS DATA_DT FROM KK WHERE DATA_DT=DATE;
|
输入:ALIAS
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT
expression1 (
TITLE 'Expression 1'
) AS alias1
,CASE
WHEN alias1 + Cx >= z
THEN 1
ELSE 0
END AS alias2
FROM
tab1
WHERE
alias1 = y
;
|
输出:tdMigrateALIAS = FALSE
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT
expression1 AS alias1
,CASE
WHEN alias1 + Cx >= z
THEN 1
ELSE 0
END AS alias2
FROM
tab1
WHERE
alias1 = y
;
|
输出:tdMigrateALIAS = TRUE
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT
expression1 AS alias1
,CASE
WHEN expression1 + Cx >= z
THEN 1
ELSE 0
END AS alias2
FROM
tab1
WHERE
expression1 = y
;
|
FORMAT和CAST
Teradata中,关键词FORMAT用于格式化列或表达式。例如,LPAD中FORMAT '9(n)'和'z(n)'分别用'0'和空格(' ')表示。
数据类型转换可使用CAST或直接数据类型([like (expression1)(CHAR(n))])进行。该功能使用CAST实现。详情参见类型转换和格式化。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT
CAST(TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) FORMAT '9(5)' )
FROM
C03_AGENT_BOND
;
SELECT
CAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) )
FROM
C03_AGENT_BOND
;
SELECT
CHAR(CAST( CAST( CND_VLU AS DECIMAL( 17 ,0 ) FORMAT 'Z(17)' ) AS VARCHAR( 17 ) ) )
FROM
C03_AGENT_BOND
;
|
输出
SELECT LPAD( CAST( TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) ) ,5 ,'0' ) AS Agt_Num FROM C03_AGENT_BOND ; SELECT CAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) ) FROM C03_AGENT_BOND ; SELECT LENGTH( CAST( LPAD( CAST( CND_VLU AS DECIMAL( 17 ,0 ) ) ,17 ,' ' ) AS VARCHAR( 17 ) ) ) AS CND_VLU FROM C03_AGENT_BOND ;
输入:FORMAT 'Z(n)9'
1 2 3 4 5 6 | SELECT
standard_price (FORMAT 'Z(5)9') (CHAR( 6 ))
,max_price (FORMAT 'ZZZZZ9') (CHAR( 6 ))
FROM
product_t
;
|
输出
1 2 3 4 5 6 | SELECT
CAST( TO_CHAR( standard_price ,'999990' ) AS CHAR( 6 ) ) AS standard_price
,CAST( TO_CHAR( max_price ,'999990' ) AS CHAR( 6 ) ) AS max_price
FROM
product_t
;
|
输入:FORMAT 'z(m)9.9(n)'
1 2 3 4 5 | SELECT
standard_price (FORMAT 'Z(6)9.9(2)') (CHAR( 6 ))
FROM
product_t
;
|
输出
1 2 3 4 5 | SELECT
CAST( TO_CHAR( standard_price ,'9999990.00' ) AS CHAR( 6 ) ) AS standard_price
FROM
product_t
;
|
输入:CAST AS INTEGER
1 2 3 4 5 | SELECT
CAST( standard_price AS INTEGER )
FROM
product_t
;
|
输出
1 2 3 4 5 | SELECT
(standard_price)
FROM
product_t
;
|
输入:CAST AS INTEGER FORMAT
1 2 3 4 5 6 7 | SELECT
CAST( price11 AS INTEGER FORMAT 'Z(4)9' ) (
CHAR( 10 )
)
FROM
product_t
;
|
输出
1 2 3 4 5 | SELECT
CAST( TO_CHAR( ( price11 ) ,'99990' ) AS CHAR( 10 ) ) AS price11
FROM
product_t
;
|

新增以下Gauss函数来转换为INTEGER:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE OR REPLACE FUNCTION
/* This function is used to support "CAST AS INTEGER" of Teradata.
It should be created in the "mig_td_ext" schema.
*/
( i_param TEXT )
RETURN INTEGER
AS
v_castasint INTEGER;
BEGIN
v_castasint := CASE WHEN i_param IS NULL
THEN NULL -- if NULL value is provided as input
WHEN TRIM(i_param) IS NULL
THEN 0 -- if empty string with one or more spaces is provided
ELSE TRUNC(CAST(i_param AS NUMBER)) -- if any numeric value is provided
END;
RETURN v_castasint;
END;
|
缩写关键字迁移
表1列出了Teradata支持的缩写关键字及其语法在GaussDB A 和 DWS中对应的语法。
Teradata语法 |
对应的GaussDB A 和 DWS语法 |
---|---|
SEL |
SELECT |
INS |
INSERT |
UPD |
UPDATE |
DEL |
DELETE |
CT |
CREATE TABLE |
CV |
CREATE VIEW |
BT |
START TRANSACTION |
ET |
COMMIT |
输入:BT
BT; -- delete from ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL where DW_Job_Seq = ${v_Group_No}; .if ERRORCODE <> 0 then .quit 12; -- insert into ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL ( Cust_Id ,Cust_UID ,DW_Upd_Dt ,DW_Upd_Tm ,DW_Job_Seq ,DW_Etl_Dt ) select a.Cust_Id ,a.Cust_UID ,current_date as Dw_Upd_Dt ,current_time(0) as DW_Upd_Tm ,cast(${v_Group_No} as byteint) as DW_Job_Seq ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Etl_Dt from ${BRTL_VCOR}.BRTL_CS_CUST_CID_UID_REL_S a where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd'); .if ERRORCODE <> 0 then .quit 12; ET;cd ..
输出
BEGIN -- BEGIN delete from ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL where DW_Job_Seq = ${v_Group_No}; lv_mig_errorcode = 0; EXCEPTION WHEN OTHERS THEN lv_mig_errorcode = -1; END; IF lv_mig_errorcode <> 0 THEN RAISE EXCEPTION '12'; END IF; -- BEGIN insert into ${BRTL_DCOR}.BRTL_CS_CUST_CID_UID_REL ( Cust_Id ,Cust_UID ,DW_Upd_Dt ,DW_Upd_Tm ,DW_Job_Seq ,DW_Etl_Dt ) select a.Cust_Id ,a.Cust_UID ,current_date as Dw_Upd_Dt ,current_time(0) as DW_Upd_Tm ,cast(${v_Group_No} as byteint) as DW_Job_Seq ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Etl_Dt from ${BRTL_VCOR}.BRTL_CS_CUST_CID_UID_REL_S a where a.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd'); EXCEPTION WHEN OTHERS THEN lv_mig_errorcode = -1; END; IF lv_mig_errorcode <> 0 THEN RAISE EXCEPTION '12'; END IF; END;
下表具体描述了这些对象名称的迁移行为。这些行为可以通过tdMigrateDollar参数来设置。
详情请参见IN/NOT IN转换。
tdMigrateDollar设置 |
对象名称 |
迁移为 |
---|---|---|
true |
$V_SQL 静态对象名称 |
"$V_SQL" |
true |
${V_SQL} 动态对象名称 |
${V_SQL} 无变化:不支持动态对象名称 |
false |
$V_SQL 静态对象名称 |
$V_SQL 无变化:参数设为false |
false |
${V_SQL} 动态对象名称 |
${V_SQL} 无变化:参数设为false |

任何以$开头的变量都被视为值。工具将通过添加ARRAY来进行迁移。
1 | SELECT $C1 from p11 where $C1 NOT LIKE ANY ($sql1);
|
输出(tdMigrateDollar = TRUE)
1 2 3 4 5 6 7 8 9 | SELECT
"$C1"
FROM
p11
WHERE
"$C1" NOT LIKE ANY (
ARRAY[ "$sql1" ]
)
;
|
输出(tdMigrateDollar = FALSE)
1 2 3 4 5 6 7 8 9 | SELECT
$C1
FROM
p11
WHERE
$C1 NOT LIKE ANY (
ARRAY[ $sql1 ]
)
;
|
1 2 3 4 5 6 7 8 9 10 11 | SELECT * FROM T1
WHERE T1.Event_Dt>=ADD_MONTHS(CAST('${OUT_DATE}' AS DATE FORMAT 'YYYYMMDD')+1,(-1)*CAST(T7.Tm_Range_Month AS INTEGER))
AND T1.Event_Dt<=CAST('${OUT_DATE}' AS DATE FORMAT 'YYYYMMDD')
AND T1.Cntpty_Acct_Name NOT LIKE ALL ( SELECT Tx_Cntpty_Name_Key FROM TEMP_NAME )
AND T1.Cntpty_Acct_Name NOT LIKE ANY ( SELECT Tx_Cntpty_Name_Key FROM TEMP_NAME )
AND T1.Cntpty_Acct_Name LIKE ALL ( SELECT Tx_Cntpty_Name_Key FROM TEMP_NAME )
AND T1.Cntpty_Acct_Name LIKE ANY ( SELECT Tx_Cntpty_Name_Key FROM TEMP_NAME )
AND T1.Col1 NOT LIKE ANY ($sql1)
AND T1.Col1 NOT LIKE ALL ($sql1)
AND T1.Col1 LIKE ANY ($sql1)
AND T1.Col1 LIKE ALL ($sql1);
|
输出
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 | SELECT
*
FROM
T1
WHERE
T1.Event_Dt >= ADD_MONTHS (CAST( '${OUT_DATE}' AS DATE ) + 1 ,(- 1 ) * CAST( T7.Tm_Range_Month AS INTEGER ))
AND T1.Event_Dt <= CAST( '${OUT_DATE}' AS DATE )
AND T1.Cntpty_Acct_Name NOT LIKE ALL (
SELECT
Tx_Cntpty_Name_Key
FROM
TEMP_NAME
)
AND T1.Cntpty_Acct_Name NOT LIKE ANY (
SELECT
Tx_Cntpty_Name_Key
FROM
TEMP_NAME
)
AND T1.Cntpty_Acct_Name LIKE ALL (
SELECT
Tx_Cntpty_Name_Key
FROM
TEMP_NAME
)
AND T1.Cntpty_Acct_Name LIKE ANY (
SELECT
Tx_Cntpty_Name_Key
FROM
TEMP_NAME
)
AND T1.Col1 NOT LIKE ANY (
ARRAY[ "$sql1" ]
)
AND T1.Col1 NOT LIKE ALL (
ARRAY[ "$sql1" ]
)
AND T1.Col1 LIKE ANY (
ARRAY[ "$sql1" ]
)
AND T1.Col1 LIKE ALL (
ARRAY[ "$sql1" ]
)
;
|
QUALIFY、CASE和ORDER BY
输入
select a.Cust_UID as Cust_UID /* UID */ ,a.Rtl_Usr_Id as Ini_CM /* */ ,a.Cntr_Aprv_Dt as Aprv_Pass_Tm /* */ ,a.Blg_Org_Id as CM_BRN_Nbr /* */ ,a.Mng_Chg_Typ_Cd as MNG_CHG_TYP_CD /* */ ,case when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'PMD' and a.Pst_Id in ('PB0101','PB0104') then 'Y' ---- , when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DEVPMD' and a.Pst_Id ='PB0106' then 'Y' ---- when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DMD' and a.Pst_Id in ('PB0201','PB0204') then 'Y' ---- , when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DEVDMD' and a.Pst_Id ='PB0109' then 'Y' ---- , else '' end as Pst_Flg /* */ ,a.Pst_Id as Pst_Id /* */ ,a.BBK_Org_Id as BBK_Org_Id /* */ from VT_CUID_MND_NMN_CHG_INF as a /* VT_ */ LEFT OUTER JOIN ${BRTL_VCOR}.BRTL_EM_USR_PST_REL_INF_S as b /* EM_ */ on a.Rtl_Usr_Id = b.Rtl_Usr_Id AND a.Blg_Org_Id = b.BRN_Org_Id AND a.Pst_Id = b.Pst_Id AND b.Sys_Id = 'privatebanking' AND b.pst_sts IN ('1','0','-2') /* 1 -2 0 */ AND b.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') qualify row_number() over(partition by a.Cust_UID,a.bbk_org_id order by case when ( a.Mng_Chg_Typ_Cd= 'PMD' and a.Pst_Id in ('PB0101','PB0104')) or ( a.Mng_Chg_Typ_Cd= 'DEVPMD' and a.Pst_Id ='PB0106') then 0 when (a.Mng_Chg_Typ_Cd= 'DMD' and a.Pst_Id in ('PB0201','PB0204')) or (a.Mng_Chg_Typ_Cd= 'DEVDMD' and a.Pst_Id ='PB0109 ') then 0 else 1 end asc ) = 1 ;
输出
SELECT Cust_UID AS Cust_UID /* UID */ ,Ini_CM /* */ ,Aprv_Pass_Tm /* */ ,CM_BRN_Nbr /* */ ,MNG_CHG_TYP_CD /* */ ,Pst_Flg /* */ ,Pst_Id AS Pst_Id /* */ ,BBK_Org_Id AS BBK_Org_Id /* */ FROM ( SELECT a.Cust_UID AS Cust_UID /* UID */ ,a.Rtl_Usr_Id AS Ini_CM /* */ ,a.Cntr_Aprv_Dt AS Aprv_Pass_Tm /* */ ,a.Blg_Org_Id AS CM_BRN_Nbr /* */ ,a.Mng_Chg_Typ_Cd AS MNG_CHG_TYP_CD /* */ ,CASE WHEN a.Blg_Org_Id = b.BRN_Org_Id AND a.Mng_Chg_Typ_Cd = 'PMD' AND a.Pst_Id IN ( 'PB0101' ,'PB0104' ) THEN 'Y' /* , */ WHEN a.Blg_Org_Id = b.BRN_Org_Id AND a.Mng_Chg_Typ_Cd = 'DEVPMD' AND a.Pst_Id = 'PB0106' THEN 'Y' /* */ WHEN a.Blg_Org_Id = b.BRN_Org_Id AND a.Mng_Chg_Typ_Cd = 'DMD' AND a.Pst_Id IN ( 'PB0201' ,'PB0204' ) THEN 'Y' /* , */ WHEN a.Blg_Org_Id = b.BRN_Org_Id AND a.Mng_Chg_Typ_Cd = 'DEVDMD' AND a.Pst_Id = 'PB0109' THEN 'Y' /* , */ ELSE '' END AS Pst_Flg /* */ ,a.Pst_Id AS Pst_Id /* */ ,a.BBK_Org_Id AS BBK_Org_Id /* */ ,row_number( ) over( partition BY a.Cust_UID ,a.bbk_org_id ORDER BY CASE WHEN( a.Mng_Chg_Typ_Cd = 'PMD' AND Q1.Pst_Id IN ( 'PB0101' ,'PB0104' ) ) OR( Q1.Mng_Chg_Typ_Cd = 'DEVPMD' AND a.Pst_Id = 'PB0106' ) THEN 0 WHEN( a.Mng_Chg_Typ_Cd = 'DMD' AND Q1.Pst_Id IN ( 'PB0201' ,'PB0204' ) ) OR( Q1.Mng_Chg_Typ_Cd = 'DEVDMD' AND a.Pst_Id = 'PB0109 ' ) THEN 0 ELSE 1 END ASC ) AS ROW_NUM1 FROM VT_CUID_MND_NMN_CHG_INF AS a /* VT_ */ LEFT OUTER JOIN BRTL_VCOR.BRTL_EM_USR_PST_REL_INF_S AS b /* EM_ */ ON a.Rtl_Usr_Id = b.Rtl_Usr_Id AND a.Blg_Org_Id = b.BRN_Org_Id AND a.Pst_Id = b.Pst_Id AND b.Sys_Id = 'privatebanking' AND b.pst_sts IN ( '1' ,'0' ,'-2' ) /* 1 -2 0 */ AND b.DW_Snsh_Dt = CAST( lv_mig_v_Trx_Dt AS DATE ) ) Q1 WHERE Q1.ROW_NUM1 = 1 ;
