If the function performs an insert, delete or update, it cannot be used in a select statement just like a column.chulett wrote:A stored function is selected in a query just like a table column and DataStage doesn't change how any of that works.
So if anyone knows how to invoke a function that both performs an upsert and returns a numerical value, please reply. I can only get it to work if I hard-wire the function parameters inside the stage with constants or job parameters - I can't seem to be able to pass them in from each incoming row of data like I want to.
What the function in my case essentially does is accept some input parameters, if they match an existing row on a table, it updates one or more rows with the parameter values and returns the value of a column from one of the existing rows. If no match is found, a sequence is incremented, the input parameters are inserted, and the sequence value is returned. I want to take that return value in either case and use it downstream in my DataStage job.
And no, I can't change the function, it's not mine, I've just been told I have to use it...