NiFi Version: 2.5.0
When transporting information from one site to another, I need to prepend a 0 to someones PIN as that site has different requirements. I query the data using a QueryRecord processor, like so:
SELECT
MsgSeqNbr, PostTime, SSN, EmployeeID,
LName, FName, MName,
CAST(TRIM(LCN) AS BIGINT) AS LCN,
LPAD(PIN, 5, '0'),
EmployeeType, ValidityCode, AgencyOwner, AgencyLocated,
BadgeCreatedBy, BadgeCreatedTime,
BadgeModifiedBy, BadgeModifiedTime,
Clearance, Error, Status
FROM FLOWFILE
However I get the following error from Calcite:
QUERY...
- Caused by:
org.apache.calcite.runtime.CalciteContentException: From line 5, column 5, column X: No match found for function signature LPAD(<CHARACTER>, <NUMERIC>, <CHARACTER>).
I've tried a couple different methods to get this to pass, explicitly defining the type as VARCHAR(5) for both the string and the pattern, using a static value to see if it will pass but the same result occurs.
The incoming Avro data has the PIN like so:
...
"PIN" : "111",
...
So I know the data type it's receiving truly is a string. Following the ticket that was created for the original implementation it seems the dev's had the same intent for how to use the function: https://issues.apache.org/jira/browse/CALCITE-5451
Anyone else encounter a similar issue with Calcite?
