Re: [EXTERNAL] Re: Problem with an SQL UDF created from a procedure within a service program

A little off-topic, but here's a technique I use:

Instead of:

when %parms = 1;

I suggest using %parmnum, as so:

when %parms < %parmnum(UseCommitmentControl);


That way if you add parameters or move things around, you have a much better chance of your code still working as expected.  I usually do it the other way (testing positive instead of negative) and so I use >=, but the concept still holds.


On 12/9/2020 2:02 PM, Alan Shore via MIDRANGE-L wrote:

Okay - I figured out a way around this situation

I created a NEW function NEXTNUMBERSQL and changed the PR/PI so that Function is now varying
Here is the code
P NextNumberSQL B export
D NextNumberSQL PI 12p 0
D Function 50 const
D Varying
D UseCommitmentControl...
D n const options(*nopass)
D HldNextNumber s like(nn_NextNum)
D HldFunction s 50
*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* Open the appropriate next numbers file, as needed
HldFunction = %trim(Function);
select;
when %parms = 1;
HldNextNumber = NextNumber(HldFunction);
other;
HldNextNumber = NextNumber(HldFunction: UseCommitmentControl);
endsl;
return HldNextNumber;
P NextNumberSQL E