Topic
  • 1 reply
  • Latest Post - ‏2011-12-05T21:51:01Z by PTM
PTM
PTM
2 Posts

Pinned topic Migrating function from MS Sql Server to Informix

‏2011-12-01T21:51:11Z |
Hello.

The following function, developed for MS Sql Server, returns a list of names separated by commas:


CREATE FUNCTION dbo.fnMemberList (@ReservationId AS INT, @MembershipNbr AS CHAR(10)) RETURNS VARCHAR(1000) BEGIN DECLARE @Names VARCHAR(1000) SET @NAMES = 
''   SELECT @Names = @Names + 
', ' + RTrim (LTrim (IsNull (M.fname, 
''))) + 
' ' + RTrim (LTrim (IsNull (M.lname, 
''))) FROM asiPlayer P INNER JOIN asaMembr M ON P.cust_code = M.cust_code AND P.mbr_code = M.mbr_code WHERE P.reservation = @ReservationId AND P.cust_code = @MembershipNbr ORDER BY M.fname, M.lname, M.mbr_code IF (Left (@Names, 2) = 
', ') BEGIN SET @Names = SubString (@Names, 3, Len (@Names) - 2) END RETURN @Names END GO

If the result set is empty, an empty string is returned.
If the result set has one row, one name is returned (e.g., "John Doe").
If the result set has multiple rows, multiple names are returned, one for each row (e.g, "John Doe, Bob Smith, Paul Williams").

I am trying to "translate" this function to Informix, but found a few hurdles.
I am new to Informix, which might explain the difficulties...
The first implementation uses a cursor:

CREATE FUNCTION fnMemberList (parReservationId INT, parMembershipNbr CHAR(10)) RETURNING CHAR(1000) AS result; DEFINE Names CHAR(1000); DEFINE CurrentName CHAR(100); LET Names = 
'';   FOREACH cursor FOR SELECT 
', ' || Trim (NVl (M.fname, 
'')) || 
' ' || Trim (NVl (M.lname, 
'')) INTO CurrentName FROM asiPlayer P INNER JOIN asaMembr M ON P.cust_code = M.cust_code AND P.mbr_code = M.mbr_code WHERE P.reservation = parReservationId AND P.cust_code = parMembershipNbr ORDER BY M.fname, M.lname, M.mbr_code   LET Names = Names || CurrentName; END FOREACH;   RETURN Names; END FUNCTION;

The problem with this implementation is that the function always returns an empty string.
The second implementation uses a cursor and a temporary table:

CREATE FUNCTION fnMemberList (parReservationId INT, parMembershipNbr CHAR(10)) RETURNING CHAR(1000) AS result; DEFINE Names CHAR(1000); DEFINE CurrentName CHAR(100); LET Names = 
''; CREATE TEMP TABLE tab (name CHAR(1000)) WITH NO LOG; INSERT INTO tab (name) VALUES (
'');   FOREACH cursor FOR SELECT 
', ' || Trim (NVl (M.fname, 
'')) || 
' ' || Trim (NVl (M.lname, 
'')) INTO CurrentName FROM asiPlayer P INNER JOIN asaMembr M ON P.cust_code = M.cust_code AND P.mbr_code = M.mbr_code WHERE P.reservation = parReservationId AND P.cust_code = parMembershipNbr ORDER BY M.fname, M.lname, M.mbr_code   UPDATE tab SET name = name || NVl (CurrentName, 
''); END FOREACH;   SELECT name INTO Names FROM tab; DROP TABLE tab; RETURN Names; END FUNCTION;

No errors are reported when the function is created. However, execution ends with error "SQL Error (-675): Illegal SQL statement in SPL routine."
Maybe temporary tables cannot be created by functions.
Any ideas how to fix implementation #1 or #2?
Any suggestions about a third implementation?

Thank you in advance.
PTM
Updated on 2011-12-05T21:51:01Z at 2011-12-05T21:51:01Z by PTM
  • PTM
    PTM
    2 Posts

    Re: Migrating function from MS Sql Server to Informix

    ‏2011-12-05T21:51:01Z  
    For those who might be interested, here is the solution:

    
    CREATE FUNCTION fnMemberList (parReservationId INT, parMembershipNbr CHAR(10)) RETURNING CHAR(1000) AS result; DEFINE Names CHAR(1000); DEFINE CurrentName CHAR(100); LET Names = 
    ''; LET CurrentName = 
    '';   FOREACH SELECT 
    ', ' || Trim (NVl (M.fname, 
    '')) || 
    ' ' || Trim (NVl (M.lname, 
    '')) INTO CurrentName FROM asiPlayer P INNER JOIN asaMembr M ON P.cust_code = M.cust_code AND P.mbr_code = M.mbr_code WHERE P.reservation = parReservationId AND P.cust_code = parMembershipNbr ORDER BY M.fname, M.lname, M.mbr_code   LET Names = Trim (Names) || Trim (NVl (CurrentName, 
    '')); END FOREACH IF (SubStr (Names, 1, 2) = 
    ', ') THEN LET Names = SubStr (Names, 3, Length (Names) - 2); END IF   RETURN Names; END FUNCTION;
    


    PTM