I'm trying to create a SqlBasicCall that casts the output from a UDF to VARCHAR(50). The UDF is a representation of the Oracle STANDARD_HASH function and the last step I would like to do is CAST the result to VARCHAR.
So to explain further, I have some SQL:
SELECT REGION_NAME FROM REGIONS
I am writing some code that will create a HASH of the column. Therefore the outcome should be
SELECT CAST(STANDARD_HASH(REGION_NAME)AS VARCHAR(50)) FROM REGIONS
The code I have so far:
//create Hash function
SqlNode[] HashFunctionOperandList = new SqlNode[1];
HashFunctionOperandList[0] = new SqlIdentifier("REGION_NAME",SqlParserPos.ZERO);
SqlBasicCall innerFunc = new SqlBasicCall(ORACLE_HASH, HashFunctionOperandList, SqlParserPos.ZERO);
//Create the Cast function
SqlNode[] functionOperandList = new SqlNode[2];
functionOperandList[0] = innerFunc;
functionOperandList[1] = new SqlBasicTypeNameSpec(SqlTypeName.VARCHAR, SqlParserPos.ZERO).getTypeName();
compliantFunction = new SqlBasicCall(new SqlCastFunction(), functionOperandList, SqlParserPos.ZERO);
ORACLE_HASH is defined as a UDF elsewhere:
public static SqlFunction ORACLE_HASH = new SqlFunction("STANDARD_HASH", SqlKind.OTHER_FUNCTION, ReturnTypes.VARCHAR_2000, null, OperandTypes.ANY, SqlFunctionCategory.USER_DEFINED_FUNCTION);
The current output is:
SELECT CAST(STANDARD_HASH("REGION_NAME") AS "VARCHAR") "REGION_NAME" FROM "REGIONS"
Where what I need it to be is:
SELECT CAST(STANDARD_HASH("REGION_NAME") AS VARCHAR(50)) "REGION_NAME" FROM "REGIONS"
I believe my SqlBasicTypeNameSpec line is wrong (I'm sure it is), but I have no idea how to create a SqlIdentifer that gives me VARCHAR(50) and without the quotes. I am sure I have fundamentally misunderstood something so thought I would ask the crowd.
