CODEWITHSUNDEEP
sqldb2pivotdb2-luwbigsql
matz3
matz3

Reputation: 100

Db2 Pivot function - fetch next statement

Under https://www.itjungle.com/2015/04/21/fhg042115-story01/ a good article on how to write an universal Db2 pivot method can be found.

Executing the following code (be aware of 'end #' instead 'end ;'):

SET SCHEMA = TESTSCHEMA;

CREATE PROCEDURE DO_PIVOT
(IN FOR_SCHEMA CHARACTER (10) , 
IN FOR_TABLE CHARACTER (10) , 
IN PIVOT_COLUMN VARCHAR (250) , 
IN VALUE_COLUMN VARCHAR (250) , 
IN AGG_FUNCTION VARCHAR (5) DEFAULT 'SUM' , 
IN GROUP_COLUMN VARCHAR (250) DEFAULT NULL ) 
LANGUAGE SQL 
MODIFIES SQL DATA 
PROGRAM TYPE SUB 
CONCURRENT ACCESS RESOLUTION DEFAULT 
DYNAMIC RESULT SETS 1 
OLD SAVEPOINT LEVEL COMMIT ON RETURN NO 

BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0 ;
DECLARE SQL_STATEMENT VARCHAR ( 5000 ) ;
DECLARE PIVOT_VALUE VARCHAR ( 20 ) ;
DECLARE PAD CHAR ( 2 ) DEFAULT ' ' ;
DECLARE C1 CURSOR FOR D1 ;
DECLARE C2 CURSOR WITH RETURN FOR D2 ;

SET SCHEMA = FOR_SCHEMA ;

-- Get the list of values available for the pivot column

-- Each value will be a column in the return set
SET SQL_STATEMENT = 'select distinct ' 
                   || PIVOT_COLUMN  
                   || ' from ' 
                   || FOR_TABLE 
                   || ' order by 1' ;

PREPARE D1 FROM SQL_STATEMENT ;
OPEN C1 ;

-- Construct a dynamic select statement for the pivot
SET SQL_STATEMENT = 'select ' ;

-- If requested, add the Group By Column 
-- to the select clause
IF GROUP_COLUMN IS NOT NULL THEN
  SET SQL_STATEMENT = SQL_STATEMENT || GROUP_COLUMN ;
  SET PAD = ', ' ;
END IF ;

-- For each possible value for the Pivot Column, 
-- add a case statement to perform the requested 
-- aggregate function on the Value Column
FETCH NEXT FROM C1 INTO PIVOT_VALUE ;
WHILE ( SQLCODE >= 0 AND SQLCODE <> 100 ) DO
  SET SQL_STATEMENT = SQL_STATEMENT 
                      || PAD 
                      || AGG_FUNCTION 
                      || '(CASE WHEN ' 
                      || PIVOT_COLUMN 
                      || ' = ''' 
                      || PIVOT_VALUE 
                      || ''' THEN ' 
                      || VALUE_COLUMN 
                      || '  END) AS ' 
                      || PIVOT_VALUE ;
  SET PAD = ', ' ;
  FETCH NEXT FROM C1 INTO PIVOT_VALUE ;
END WHILE ;
CLOSE C1 ;

-- Specify the table to select from
SET SQL_STATEMENT = SQL_STATEMENT 
                    || ' from ' 
                    || FOR_TABLE ;

-- If requested, add the Group By Column
-- to the select clause
IF GROUP_COLUMN IS NOT NULL THEN
  SET SQL_STATEMENT = SQL_STATEMENT 
                      || ' group by ' 
                      || GROUP_COLUMN 
                      || ' order by ' 
                      || GROUP_COLUMN;
END IF ;

PREPARE D2 FROM SQL_STATEMENT ;
OPEN C2 ;

END #

LABEL ON ROUTINE DO_PIVOT 
( CHAR(), CHAR(), VARCHAR(), VARCHAR(), VARCHAR(), VARCHAR() )  
IS 'Perform a General Purpose Pivot';

COMMENT ON PARAMETER ROUTINE DO_PIVOT 
( CHAR(), CHAR(), VARCHAR(), VARCHAR(), VARCHAR(), VARCHAR() ) 
(FOR_SCHEMA IS 'Schema for Table' , 
FOR_TABLE IS 'For Table' , 
PIVOT_COLUMN IS 'Name of Column to be Pivoted' , 
VALUE_COLUMN IS 'Column to be Aggregated for Pivot' , 
AGG_FUNCTION IS 'Use Aggregate Function' , 
GROUP_COLUMN IS 'Group on Column' ) ;

produces the error message:

DB21034E  The command was processed as an SQL statement because it was
not a valid Command Line Processor command.  During SQL processing it
returned:
SQL0104N  An unexpected token "FROM" was found following "    FETCH
NEXT".
Expected tokens may include:  ".".  LINE NUMBER=50.  SQLSTATE=42601

Any suggestions?

Upvotes: 1

Views: 683

Answers (1)

mao
mao

Reputation: 12267

For Db2-LUW v11.1 and above, to get the sproc to compile, you can make the changes listed below. This is only for getting a successful compile. I've not checked the functionality.

  • Remove (or comment-out) PROGRAM TYPE SUB

  • Remove (or comment-out) CONCURRENT ACCESS RESOLUTION DEFAULT

  • Change FETCH NEXT FROM to FETCH (in two places) , or use FETCH FROM

  • Remove (or comment-out) LABEL ON ROUTINE statement

  • Remove (or comment-out) COMMENT ON PARAMETER ROUTINE statement

Upvotes: 1

Related Questions