Hi I am trying to write some code that will take a MySQL SQL statement that includes a SHA1 hash statement and have it translate successfully to Oracle and in doing so add a cast function.
Eg: select sha1(address) from locations limit 10 Should translate to: SELECT CAST(STANDARD_HASH(ADDRESS) AS VARCHAR(200)) FROM LOCATIONS FETCH FIRST 10 ROWS ONLY I have 90% done but am struggling with the last part. I have via creating my own SqlNodes done the following: SELECT CAST(STANDARD_HASH("LOCATIONS"."ADDRESS") AS "VARCHAR(200)") "ADDRESS" FROM “LOCATIONS" This has been achieved by the following code: …skipping setup code… //create Hash function SqlNode[] HashFunctionOperandList = new SqlNode[1]; HashFunctionOperandList[0] = sqlIdentifier; //ORACLE_HASH is a UDF defined elsewhere SqlBasicCall innerFunction = new SqlBasicCall(ORACLE_HASH, HashFunctionOperandList, new SqlParserPos(0, 0)); //Create the Cast function SqlNode[] functionOperandList = new SqlNode[2]; functionOperandList[0] = innerFunction; functionOperandList[1] = new SqlIdentifier("VARCHAR(200)", SqlParserPos.ZERO); compliantFunction = new SqlBasicCall(new SqlCastFunction(),functionOperandList, new SqlParserPos(0, 0)); …skipping to output code… SqlDialect translationDialect; translationDialect = OracleSqlDialect.DEFAULT;; String Query = compliantFunction.toSqlString(translationDialect).toString(); My key issue seems to be how I get VARCHAR(200) in my cast function, secondly everything included the CAST AS has been quoted. I’ve tried SO for support and according to the Calcite website, It recommended I try here. So any help would be appreciated? Thanks