Hi People! quick question relating to UDF. Anytime I try to apply any transformation on input parameter , the call on the UDF fails. I need to recast an int parameter as varchar in order to use it as “key” for a JSON stack.
CREATE OR REPLACE FUNCTION f_ratings_draft_3
( p_ratingweight INT, p_ratingcategory int )
RETURNS VARCHAR
RETURN
select CONVERT_FROM(‘{“0”:[“UNKNOWN”,“UNKNOWN”],“1”:[“AAA”,“AAA”],“2”:[“AA+”,“AA”],“3”:[“AA”,“AA”],“4”:[“AA-”,“AA”],“5”:[“A+”,“A”],“6”:[“A”,“A”]
,“7”:[“A-”,“A”],“8”:[“BBB+”,“BBB”],“9”:[“BBB”,“BBB”],“10”:[“BBB-”,“BBB”],“11”:[“BB+”,“BB”],“12”:[“BB”,“BB”],“13”:[“BB-”,“BB”],“14”:[“B+”,“B”]
,“15”:[“B”,“B”],“16”:[“B-”,“B”],“17”:[“CCC”,“CCC”],“18”:[“CC”,“CC”],“19”:[“C”,“C”],“20”:[“D”,“D”]}’, ‘json’)[CAST(p_ratingweight as VARCHAR)][p_ratingcategory]
select f_ratings_draft_3(3,0)
error: org.apache.calcite.rex.RexCall cannot be cast to org.apache.calcite.rex.RexLiteral