DB2 - Aggregate with Case in Cursor

Question

I am trying to create a stored procedure that returns a data set, using a cursor, that contains multiple aggregate functions over subqueries. The query works when executed as a standalone script, but when putting it into the stored procedure format using a cursor it doesn't work. When using an aggregate, the code runs fine. When using an aggregate on a case statement, the stored procedure fails to be created.

Input Table data:

Province |  Contract Date
---------------------------
Ontario  |  June 11th, 2017
Ontario  |  June 21st, 2017
Quebec   |  July 12th, 2017

Query:

DECLARE C2 CURSOR WITH HOLD WITH RETURN TO CALLER FOR
SELECT 
    count(province) as province_total
    FROM (
       SELECT 
          contract.province,
          contract.contract_date
       WHERE contract.CON_CONTRACT_DATE >='2015-01-01' 
       AND contract.CON_CONTRACT_DATE < '2018-11-01'
    );

Returns:

Province_Total |  
----------------
3              |

So this gives me the Province total. I am trying to do statistics on how many times a particular province occurs. I am doing so with the following query:

CREATE PROCEDURE test
DYNAMIC RESULT SETS 1  
BEGIN

DECLARE C1 CURSOR WITH RETURN TO CALLER FOR
SELECT 
    count(province) as province_total,
    sum(case province when 'Ontario' then 1.0 else 0.0 end) as ontario_total,
    sum(case province when 'Quebec' then 1.0 else 0.0 end) as quebec_total
    FROM (
       SELECT 
          contract.province,
          contract.contract_date
       FROM dbo.contract as contract
       WHERE contract.CON_CONTRACT_DATE >='2015-01-01' 
       AND contract.CON_CONTRACT_DATE < '2018-11-01'
    );
   OPEN C1;
   END

What I should be getting is:

Province_Total | Ontario_Total | Quebec_Total
----------------------------------------------
3              | 2             | 1

But I'm getting an error on trying to create the procedure. Specifically:

SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "".  Expected tokens may include:  "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.13.80

From my experience with this error message on DB2, it will be thrown when something is syntactically "wrong". The end-of-statement character ';' is otherwise recognized.

Is there any way to obtain my desired result in DB2? The use of the cursor in general is required as I need the result set to return

Any advice would be greatly appreciated. Thanks.

Edit: Using DB2 9.5

Side note: the statement you've presented doesn't require the use of a subquery. Additionally, you don't need to select contract.contract_date, since you don't output the column. I don't know if that's just from your attempts to get a minimal example, or if your query actually looks like that, though. — Clockwork-Muse

Mark Barinstein Mark Barinstein · Accepted Answer · 2018-12-06T15:58:06

2 Problems.

You must change the default statement separator in your to some new one like in the example.

FROM clause is missing from your example.

Should be something like this if you run it from DB2 CLP.

--#SET TERMINATOR @
CREATE PROCEDURE test
DYNAMIC RESULT SETS 1  
BEGIN

DECLARE C1 CURSOR WITH RETURN TO CALLER FOR
SELECT 
    count(province) as province_total,
    sum(case province when 'Ontario' then 1.0 else 0.0 end) as ontario_total,
    sum(case province when 'Quebec' then 1.0 else 0.0 end) as quebec_total
    FROM (
       SELECT 
          contract.province,
          contract.contract_date
    FROM MY_TABLE
       WHERE contract.CON_CONTRACT_DATE >='2015-01-01' 
       AND contract.CON_CONTRACT_DATE < '2018-11-01'
    );
   OPEN C1;
   END@

DB2 - Aggregate with Case in Cursor

1 Answers