Declaring a variable in SQL DB2

[ross.oneill]

Hi,
I am having trouble with a simple task of declaring a variable. Is
this possible?

Here is what I want to do.

DECLARE start_date date;
DECLARE end_date date;

SET start_date = '2005-01-01'
SET end_date = '2006-01-01'

SELECT *
FROM ONEILLR.EMP_ACT
where EMENDATE between start_date and end_date;

Thanks
Ross

[Philip Nelson]

ro*********@gmail.com wrote:

Hi,
I am having trouble with a simple task of declaring a variable. Is
this possible?

Here is what I want to do.

DECLARE start_date date;
DECLARE end_date date;

SET start_date = '2005-01-01'
SET end_date = '2006-01-01'

SELECT *
FROM ONEILLR.EMP_ACT
where EMENDATE between start_date and end_date;

Thanks
Ross

Ross,

I'm assuming you are talking in the context of a stored procedure or a user
defined function ?

create procedure db001.sample
(
in inval INTEGER,
out reply char(5)
)
SPECIFIC DB001.Sample
BEGIN
--
-- Define working storage
--
DECLARE SQLCODE INTEGER;
DECLARE SQLSTATE CHAR(5);
DECLARE v_col1 INTEGER;
DECLARE v_col2 INTEGER;
DECLARE v_sqlcode INTEGER DEFAULT 0;
--
-- Here's a cursor (for processing multiple rows)
--
DECLARE c01cursor CURSOR FOR
SELECT
COL1
FROM
DB001.TABLE1
WHERE
COL2 = inval
ORDER BY COL3
;
--
-- Open cursor
--
OPEN c01cursor;
--
-- Fetch first DAD
--
FETCH FROM c01cursor INTO v_col1;
SET v_sqlcode = SQLCODE;
--
-- Process all rowss
--
WHILE ( v_sqlcode = 0 ) DO
--
-- obviously you want to do some real work here
--
FETCH FROM c01cursor INTO v_col1;
SET v_sqlcode = SQLCODE;
END WHILE;
--
-- Close cursor
--
CLOSE c01dad;
--
-- And here is a singleton select
--
SET (v_col1, v_col2) =
( SELECT col1, col2
FROM
DB001.TABLE1
WHERE COLKEY = keyval
);
SET reply = '00000';

END#

HTH

Phil

[Serge Rielau]

ro*********@gmail.com wrote:

Hi,
I am having trouble with a simple task of declaring a variable. Is
this possible?

Here is what I want to do.

DECLARE start_date date;
DECLARE end_date date;

SET start_date = '2005-01-01'
SET end_date = '2006-01-01'

SELECT *
FROM ONEILLR.EMP_ACT
where EMENDATE between start_date and end_date;

Thanks
Ross

Inside of an object (like a trigger or routine): Yes
DB2 today does not support "global" variables.
The standard recommendation is to use a regular table with one row for
"public" varibales, that is a variable which has the same value for all
sessions; or a global temp table for "private" variables.
In practically, when referring to such a "variable" table inside of a
procedure, you want to read the row into a local variables first using
SELECT INTO instead of adding joins all over the map.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab