469,271 Members | 1,425 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Declaring a variable in SQL DB2

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

Nov 12 '05 #1
2 50036
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
Nov 12 '05 #2
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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by R.G. Vervoort | last post: by
jacoder
9 posts views Thread by jacoder | last post: by
7 posts views Thread by billq | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.