Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Insert a String into another String with the Query Record Processor

avatar
Contributor

Hey Everyone,

i would like insert a string into another string with the query Record Processor with the sql function STUFF but i run into an error. Anyone have an Idea how i can adjust the sql statemant or how i can this string manipulation with the nifi expression language

 

I want to insert a colon in a column with the time of day

from thatto the
001500:15

 

This is my input Data from an json file

 

[ {
  "store_id" : 1011,
  "date" : 20220915,
  "start_time" : "0015",
  "start_end" : "0015",
  "out" : 0
}, {
  "store_id" : 1011,
  "date" : 20220915,
  "start_time" : "0030",
  "start_end" : "0030",
  "out" : 0
}]

 

This is my Sql expression

 

SELECT 
 "store_id",
 "date",
 STUFF("start_time",2,0,':') as "start",
 "start_end",
 "out"
FROM FLOWFILE

 

This is the error message i got

 

QueryRecord[id=46658fcd-0183-1000-0000-000015e8db65] Unable to query StandardFlowFileRecord[uuid=641e1c71-ff9b-45dc-a948-e527eff450f6,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1663335669504-61321, container=default, section=905], offset=280506, length=7780],offset=0,name=aa02839a-3f71-4e8d-860a-9ecee9ba745f,size=7780] due to java.sql.SQLException: Error while preparing statement [SELECT "store_id", "date", STUFF("start_time",2,0,':') as "start", "start_end", "out" FROM FLOWFILE]: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature STUFF(, , , ) ↳ causes: org.apache.calcite.runtime.CalciteContextException: From line 4, column 3 to line 4, column 29: No match found for function signature STUFF(, , , ) ↳ causes: java.sql.SQLException: Error while preparing statement [SELECT "store_id", "date", STUFF("start_time",2,0,':') as "start", "start_end", "out" FROM FLOWFILE] ↳ causes: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Error while preparing statement [SELECT "store_id", "date", STUFF("start_time",2,0,':') as "start", "start_end", "out" FROM FLOWFILE]

 

That is the expected output as a csv file

store_iddatestart_timestart_endout
10012022091500:1500:150

 

Thanks for help

1 ACCEPTED SOLUTION

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
1 REPLY 1

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login