Can a good soul over there show me how I can do something equivalent of MySQL GROUP_CONCAT using a DB2 function or procedure ?
Any insights ?
Any insights ?
CREATE FUNCTION MySchema/MyUDF (
PARCol2 CHAR(5) )
RETURNS VARCHAR(1024)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
BEGIN
DECLARE ReturnVal VARCHAR(1024) NOT NULL DEFAULT '';
FOR CsrC1 AS C1 CURSOR
FOR SELECT MyCol1
FROM MyTable
WHERE MyCol2 = ParCol2
DO SET ReturnVal = ReturnVal Concat CsrC1.MyCol1;
END FOR;
RETURN LTRIM(ReturnVal);
END ;
Select Distinct MyCol2, MyUDF(MyCol2)
From MyTable
CREATE FUNCTION MySchema/MyUDF (
Table_Name varchar(10),
Group_Concat_Field varchar(10),
Group_Field varchar(10),
Group_Field_Value varchar(32) )
...
set sqlstm =
'SELECT ' concat trim(Group_Concat_Field) concat
' FROM ' concat trim(Table_Name) concat
' WHERE ' concat trim(Group_Field) concat
' = ''' concat trim(Group_Field_Value) concat '''';
FOR CsrC1 AS C1 CURSOR
FOR sqlstm
DO SET ReturnVal = ReturnVal Concat CsrC1.Group_Concat_Field;
END FOR;
...
Select Distinct MyUDF(MyTable, MyCol2, 'MyCol1', MyCol1)
From MyTable
Comment