473,847 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 54240
ro*********@gma il.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*********@gma il.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2614
by: R.G. Vervoort | last post by:
is it possible to declare a variable in the onchange event of a <select> and use it later in php I would like to put the value of the selected option (in <select>) in a variable and later when i activate a php function use this variable Looks inpossible but who knows maybe it is possible roy
5
3093
by: fred | last post by:
I don't know if I'm doing this correctly. I have a little programming experience in python, c++ and some others but this is my first time with javascript. I'm trying have my website detect the user's browser and assign a variable with different items if it's Internet Explorer. My code works with firefox, opera and others but not IE. <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <title>Untitled...
6
1886
by: Steve Jorgensen | last post by:
Many of the regulars here have explained that declaring variables using As New .... is a bad idea, and some have given some good explanations, but I wanted add one more demonstration to the mix. This example is less practical than some, but more illustrative than most. I came up with this to show a fellow programmer why I think he's using As New ... too much, and I though I might as well share it here, too. First create a class as...
9
2065
jacoder
by: jacoder | last post by:
i amm on my 4th C class ever;) i have taken to the language ok but im stuck trying to declare the values of my 3 variables hers my effort HOPE SUMONE CAN CORRECT THIS MESSY CODE & LEND A HELPING HAND TIA #include <stdio.h> #ifdef _WIN32 #include <conio.h> #else #include <curses.h> #endif
2
2356
by: vlsidesign | last post by:
Here is my a portion of my program: #include <stdio.h> main() { int fahr, celsius; int lower, upper, step; int fc_conv(int fahr); ... snip ... }
3
1810
by: mamun | last post by:
Hi all, I am trying to create variables dynamically. This is needed because the user interface can have ten different textboxes with name as txt1, txt2 and so on. I would like to get values of all and then run in single transaction. for (int i = 1; i <= 10; i++) { (qry + i)= "insert into table1(field1) values(value1)";
8
7527
by: SM | last post by:
I've always wonder if there is diference when declaring and initializing a varible inside/outside a loop. What's a better practice? Declaring and initializing variables inside a loop routine, like this: for(var i=0; i<list; i++) { var name = list; }
7
1595
by: billq | last post by:
Hello, I ran across a code snippet with declared a variable like private Viewport? viewport I do not understand the use of the ? in the code. Thanks Bill
2
2307
by: cbrrr | last post by:
I have the variables declared twice where i think they should be. If I declare them global I get an uninitialized error, the other position gives me a declaration error. Im using visual C++ express 08 on MS. using namespace std; using std::cout; using std::cin; using std::endl; char indicator = 'y'; while( indicator == 'y' || indicator == 'Y')
4
6188
by: Busbait | last post by:
Hi I am trying to declare a variable as a FileDialog object in VB for MS Access 2007 Dim fd As FileDialog But, I am receiving an error message “ Compiler error : User-Defined type not defined” Can you please help here?
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9735
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10996
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5730
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4541
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4133
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.