Re: Updating single row/column in table A with values from single column but multiple rows in table B

Chuck,
 
I wanted to thank you for the example of how to create an SQL function for my particular situation. With a little bit of tweaking, it worked marvelously well. After I got it working, I shared the technology around the office, giving credit where credit was due, of course.
 
Regards,
 
Fred


________________________________
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Monday, May 7, 2012 5:37 PM
Subject: Re: Updating single row/column in table A with values from single column but multiple rows in table B

On 07 May 2012 10:25, fred clemens wrote:

I am trying to construct an UPDATE statement to update a single
column in a single row in table A with concatenated values from a
single column in multiple rows in table B. In googling the subject,
I have found a concept in MYSQL called Group_Concat that seems to
accomplish that task, although I only found examples where it was
used in Select statements, not Update statements. I looked in the
DB2 for i SQL reference and could find nothing about this concept.
Is there a way to accomplish this using DB2 for I SQL?? Below is
one of my attempts to try to use the Group Concat:

UPDATE MLPFLKSCD A
  SET A. FSADIN =
    GROUP_CONCAT(
      SELECT B.FXDATA
      FROM MLPFLOCKAD B
      WHERE A.FSCMP=B.FXCMP      AND A.FSLOC=B.FXLOC
        AND A.FSFARMNO=B.FXFARMNO  AND A.FSBARNID=B.FXBARNID
        AND A.FSYEAR=B.FXYEAR      AND A.FSNUM=B.FXNUM
        AND A.FSPENID=B.FXPENID    AND A.FSSEX=B.FXSEX
        AND A.FSTSLT=B.FXTSLT    AND A.FSSTDT=B.FXSDAT
    )

  A function specific to the task, can replace the more generic effect
of that Group_Concat [aggregate] function.  For example:

<code>

    create function MLPFLOCKAD_CAT_FXDATA
    ( FSCMP ...    , FSLOC ...
    , FSFARMNO ... , FSBARNID ...
    , FSYEAR ...  , FSNUM ...
    , FSPENID ...  , FSSEX ...
    , FSTSLT ...  , FSSTDT ...
    ) returns varchar( ... )
    language SQL
      ...
    begin
    declare RtnCat varchar ( ... ) default '';
      For Cat as CatFXdata cursor for
      SELECT B.FXDATA
      FROM MLPFLOCKAD B
      WHERE FSCMP=B.FXCMP      AND FSLOC=B.FXLOC
        AND FSFARMNO=B.FXFARMNO AND FSBARNID=B.FXBARNID
        AND FSYEAR=B.FXYEAR    AND FSNUM=B.FXNUM
        AND FSPENID=B.FXPENID  AND FSSEX=B.FXSEX
        AND FSTSLT=B.FXTSLT    AND FSSTDT=B.FXSDAT
      Do
      /* Assumed: no FXDATA will have colon as suffix */
      set RtnCat = RtnCat CONCAT Cat.FXDATA CONCAT ':' ;
      End For;
      Return Trim( trailing ':' from RtnCat) ;
    End

</code>

  The function could then be invoked in the given update request:

    UPDATE MLPFLKSCD A
    SET A.FSADIN =
        MLPFLOCKAD_CAT_FXDATA(
          FSCMP    , FSLOC
        , FSFARMNO  , FSBARNID
        , FSYEAR    , FSNUM
        , FSPENID  , FSSEX
        , FSTSLT    , FSSTDT )
    -- WHERE ...

  I use an almost identical function source, a routine called PARMLIST,
to generate a string describing the parameter declarations from the
SYSPARMS data [from SYSROUTINE data].  So if I request to SELECT
PARMLIST('PARMLIST','MyLibName') FROM SYSIBM/SYSDUMMY1, for example to
get the parameter list of just that one function, the result\report is:

  ....+....1....+....2....+....3....+....4....+....5....+....6. ...
  PARMLIST
  (CHARACTER VARYING(128),CHARACTER VARYING(128),CHARACTER VARYING(1000))
  ********  End of data  ********

Regards, Chuck