Db/2 – Query – Get Top N Rows for each group ( Stored Procedure )

Background

In our last post we tackled a nominal TOP N Query using a Function.

In this post we will do same using a Stored Procedure.

BTW, here is the link to the original post.

SQL

Stored Procedure

queryMaxByYearCorrelatedJoin_GroupByActualColumn

Outline

  1. Stored Procedure Declaration
    • Parameters
      • Year Begin
      • Year End
      • Number of records for each year
    • Number of Dynamic Results :- 1
    • Language :- SQL
  2. Temporary Table Creation
    • Declare Temporary Table
      • On Commit
        • Preserve
        • Or Discard Rows
      • With Replace
        • If table exists, please replace
      • Not Logged
  3. Create Index on Temporary Table
  4. Populate Temporary Table
  5. Cursor
    • Declare Cursor against result set that we will returning to client
      • WITH RETURN TO CLIENT
    • Open Cursor

Code


 -- DROP PROCEDURE queryMaxByYearCorrelatedJoin_GroupByActualColumn
-- //

CREATE OR REPLACE PROCEDURE queryMaxByYearCorrelatedJoin_GroupByActualColumn
(
	  @yearBegin INT  DEFAULT 2015
	, @yearEnd   INT  DEFAULT 9999
	, @seqNbr    int default 3

)
DYNAMIC RESULT SETS 1

LANGUAGE SQL

BEGIN 

	DECLARE @id        int;

	DECLARE @idMax     int;

	DECLARE @iYearBase int;
	DECLARE @iYear     int;
	DECLARE @iYearCurrent int;

    DECLARE @dtCurrent DATE;

	DECLARE @dtDate    DATE;

	DECLARE GLOBAL TEMPORARY TABLE SESSION.YEAR
	(

	 	  id   		 int not null 

			GENERATED BY DEFAULT
			AS IDENTITY
	          (
	          	  START WITH 1
	          	, INCREMENT BY 1
	      	  )

		, fiscalYear int not NULL 

	)
	ON COMMIT PRESERVE ROWS
	WITH REPLACE
    NOT LOGGED
    ;	

    CREATE UNIQUE INDEX IDX_YEAR
    ON SESSION.YEAR
    (
    	fiscalYear
    )
    ;

	/*
		Populate Temp TABLE
	*/
    SET @id = 1;

    SET @iYear = @yearBegin;

    WHILE (@iYear <= @yearEnd) DO

	    insert into SESSION.YEAR
		(
			fiscalYear
		)
		SELECT *

		FROM  (
					VALUES
					(
						@iYear
					)
			  ) t1
			  (			

			      fiscalYeal
			  )
		;

		SET @id = @id + 1;

		SET @iYear = @iYear + 1;

	END WHILE;

	/*
		data to return to client will be in cursor
	*/
 	BEGIN

		/*
			Declare cursor
		*/
		DECLARE cur CURSOR

		WITH RETURN TO CLIENT FOR

		    WITH cteYearCustomer
		    (
		          InvoiceDateKeyYear
		        , SaleKey
		        , CustomerKey
		        , Profit
		        , sequenceNbr
		    )
		    as
		    (
		        select  

		              tblFS.InvoiceDateKeyYear AS InvoiceDateKeyYear

		            , tblFS.SaleKey AS SaleKey

		            , tblFS.CustomerKey AS CustomerKey

		            , tblFS.PROFIT AS PROFIT

		            , ROW_NUMBER()
		                OVER
		                (
		                    PARTITION BY
		                          tblFS.InvoiceDateKeyYear

		                    ORDER BY
		                            tblFS.PROFIT DESC
		                )                   

		        from   "Fact"."SaleLargeRowStoreClusteredPartition"  tblFS

		        INNER JOIN SESSION.YEAR tblYear

		        		ON tblFS.InvoiceDateKeyYear = tblYear.fiscalYear
		    )    

		    SELECT

		          tblFS.INVOICEDATEKEYYEAR

		        , tblFS.SaleKey

		        , tblFS.CustomerKey

		        , tblFS.PROFIT

		        , tblFS.sequenceNbr     

		    from cteYearCustomer tblFS

		    WHERE tblFS.sequenceNbr <= @seqNbr

		    ;

		/*
			Open Cursor
		*/
		OPEN cur;	

	END
	;

END

//

Invoke


CALL queryMaxByYearCorrelatedJoin_GroupByActualColumn(2012, 2013, 4)

Output

Query_Output_20180922_0641PM

Troubleshooting

There is quite a bit of errors that can be encountered when developing programmable objects in DB/2 LUW.

Here are some of them.

SQL Error [42710]: The name of the object to be created is identical to the existing name

Error Image

error_42710.PNG

Error Text


Query execution failed

Reason:
SQL Error [42710]: The name of the object to be created is identical to the existing name "SESSION.YEAR" of type "DECLARED TEMPORARY TABLE".. SQLCODE=-601, SQLSTATE=42710, DRIVER=4.21.29

Remediation

Outline

  1. Add the “WITH REPLACE” clause to your Temporary table definition

SQL

	DECLARE GLOBAL TEMPORARY TABLE SESSION.YEAR
	(

	 	  id   		 int not null 

			GENERATED BY DEFAULT
			AS IDENTITY
	          (
	          	  START WITH 1
	          	, INCREMENT BY 1
	      	  )

		, fiscalYear int not NULL 

	)
	ON COMMIT PRESERVE ROWS
	-- WITH REPLACE
    NOT LOGGED
    ;	

with


	DECLARE GLOBAL TEMPORARY TABLE SESSION.YEAR
	(

	 	  id   		 int not null 

			GENERATED BY DEFAULT
			AS IDENTITY
	          (
	          	  START WITH 1
	          	, INCREMENT BY 1
	      	  )

		, fiscalYear int not NULL 

	)
	ON COMMIT PRESERVE ROWS
	WITH REPLACE
    NOT LOGGED
    ;	

 

References

  1. Temporary Table
    • Micro focus
      • Define Global Temporary Table
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s