MQSeries.net :: View topic - eSQL Function with a parameter that is another function

bradhack · Posted: Wed Sep 01, 2004 2:36 pm Post subject:

I don't even know if this is possible (I just assumed that it was), but I'm having trouble calling a reusable eSQL function when one of my parameters is created by another function. Here's the deal:

CREATE FUNCTION fEntAtt(cCode CHAR, cValue CHAR) RETURNS CHAR
--Uses 2 arguements, Code and Value, to generate a sequence of 3 fields: Length, EntAttCode, & Value
BEGIN
DECLARE cEntAttString CHAR;
SET Environment.LastSuccessfulEntAttCode = cCode;
IF LENGTH(cValue) > 0 THEN
SET cEntAttString = RIGHT('0000' || CAST(LENGTH(cValue) + 10 AS CHAR),4) || cCode || cValue;
ELSE
SET cEntAttString = '';
END IF;
RETURN cEntAttString;
END;

This works fine when my input parameters are obtained from my XML input however, when I need to perform other formatting on the parameter field first, the function deosn't return anything.

Here's are two sample calls, the first works the second doesn't:

1. Set Environment.EntAtt = fEntAtt('010654',iAnnuity.SurrenderValue);

2. Set Environment.EntAtt = fEntAtt('020003',fParticipantRole(Environment.Annuitant.RoleCode));

The second function is as follows:
CREATE FUNCTION fParticipantRole(cRole CHAR) RETURNS CHAR
--Converts Participant Role to user-friendly text
BEGIN
DECLARE cRoleOut CHAR;
SET cRoleOut =
CASE cRole
WHEN '7' THEN 'Primary Beneficiary'
WHEN '18' THEN 'Owner'
WHEN '27' THEN 'Annuitant'
END;
RETURN cRoleOut;
END;

It works on its own, but when it's called as a parameter, the 1st function will not work.

As a workaround, I've set a variable to the results of the second function, and used that as input to the first. However, I'd like to be able to combine the two steps, if anyone knows how.

Thanks for your help.