search

databrickslabs / remorph

Accelerates migrations to Databricks by automating code conversion and migration validation
Other
47 stars 31 forks source link

[BUG]: #Tredence . Issue during conversion of snowflake TRIM function #1187

Closed Mailendiran98 closed 2 weeks ago

Mailendiran98 commented 2 weeks ago

Is there an existing issue for this?

Category of Bug / Issue

TranspileParserError

Current Behavior

Input query:

SELECT iff(TRIM(src1.VALUE:TypeCd,'"') = 'null',NULL,TRIM(src1.VALUE:TypeCd,'"')) as TypeCd FROM STAGE.SO_T, LATERAL FLATTEN( INPUT => CHILD:OrdEvntHdr) src1, LATERAL FLATTEN( INPUT => CHILD:OrdEvntHdr[index].OrdEvntMsgCtrl) src2 WHERE substr(TRIM(src2.VALUE:SrcSysNm,'"'),4,3) IN ('PRD','PRA');

Output:

SELECT IF(TRIM('"' FROM src1.VALUE:TypeCd) = 'null', NULL, TRIM('"' FROM src1.VALUE:TypeCd)) AS TypeCd FROM STAGE.SO_T , LATERAL VARIANT_EXPLODE(CHILD:OrdEvntHdr) AS src1 , LATERAL VARIANT_EXPLODE(CHILD:'OrdEvntHdr[index].OrdEvntMsgCtrl') AS src2 WHERE SUBSTR(TRIM('"' FROM src2.VALUE:SrcSysNm), 4, 3) IN ('PRD', 'PRA') ;

Expected Behavior

we are getting FROM clause in converted DML (IF(TRIM('"' FROM src1.VALUE:TypeCd)) . which seems to be weird. As discussed, please check on this.

Steps To Reproduce

No response

Relevant log output or Exception details

No response

Sample Query

No response

Operating System

Windows

Version

latest via Databricks CLI

sriram251-code commented 2 weeks ago

@Mailendiran98 as disscussed on last call. This is not an issue. For more information please reffer (https://docs.databricks.com/en/sql/language-manual/functions/trim.html#syntax)