473,854 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Variables in SQL???

Another of those "What's the DB2 equivalent to this Sybase syntax"

I use local variables in Sybase SQL scripts to hold values for use
later in the script.

For example:

declare @timenow datetime
select @timenow = getdate()
delete from TableA where activity_date < @timenow
delete from TableB where activity_date < @timenow
delete from TableC where activity_date < @timenow

I've used a datatime variable @timenow to hold a value for later
use, but it could have used an int, char, etc (any datatype). Are
local variables used in DB2 scripts, or do I need to drop the value
into a temp table?

Thanks.
Oct 2 '08 #1
5 29214
On Oct 2, 11:24*am, Richard <rmcgor...@gmai l.comwrote:
Another of those "What's the DB2 equivalent to this Sybase syntax"

I use local variables in Sybase SQL scripts to hold values for use
later in the script.

For example:

declare @timenow datetime
select @timenow = getdate()
delete from TableA where activity_date < @timenow
delete from TableB where activity_date < @timenow
delete from TableC where activity_date < @timenow

I've used a datatime variable @timenow to hold a value for later
use, but it could have used an int, char, etc (any datatype). *Are
local variables used in DB2 scripts, or do I need to drop the value
into a temp table?

Thanks.
Not *quite* the same, but pretty close:

BEGIN ATOMIC
DECLARE V_TIMENOW TIMESTAMP;--
SET V_TIMENOW = CURRENT_TIMESTA MP;--
DELETE FROM TABLEA WHERE ACTIVITY_DATE < V_TIMENOW;--
DELETE FROM TABLEB WHERE ACTIVITY_DATE < V_TIMENOW;--
DELETE FROM TABLEC WHERE ACTIVITY_DATE < V_TIMENOW;--
END;

If ACTIVITY_DATE truly is just a date and not a timestamp, then (after
you change the column name ;-) you'll want to do this:

BEGIN ATOMIC
DECLARE V_DATE DATE;--
SET V_DATE = CURRENT_DATE;--
DELETE FROM TABLEA WHERE ACTIVITY_DATE < V_DATE;--
DELETE FROM TABLEB WHERE ACTIVITY_DATE < V_DATE;--
DELETE FROM TABLEC WHERE ACTIVITY_DATE < V_DATE;--
END;

One thing about the BEGIN ATOMIC is it's a black box output-wise. All
you'll get after it completes is a "statement completed successfully"-
type message.

--Jeff
Oct 2 '08 #2
That worked. Thanks. :-)

A couple of follow-up questions:

How would I display the value stored in a variable? In Sybase I could
"select" it, or "print" it. The select gives me an error and I don't
see the print option in the "SQL Reference Volume 2" manual. No other
commands in Volume 2 are popping out as obvious options. I must be
missing it.
Why do I need to type

SELECT current date FROM sysibm.sysdummy 1

in my script, instead of

SELECT current date

to display the current date, yet in your example you can say

SET V_DATENOW = current date

Is it just a difference between the SET versus SELECT command?

Thanks.
Oct 2 '08 #3
On Oct 2, 2:24 pm, Richard <rmcgor...@gmai l.comwrote:
Another of those "What's the DB2 equivalent to this Sybase syntax"

I use local variables in Sybase SQL scripts to hold values for use
later in the script.

For example:

declare @timenow datetime
select @timenow = getdate()
delete from TableA where activity_date < @timenow
delete from TableB where activity_date < @timenow
delete from TableC where activity_date < @timenow

I've used a datatime variable @timenow to hold a value for later
use, but it could have used an int, char, etc (any datatype). Are
local variables used in DB2 scripts, or do I need to drop the value
into a temp table?

Thanks.
In v9.5, db2 support variable.

create variable sch.timenow date;

set sch.timenow = current date;
delete from t1 where activate_date < sch.timenow;
delete from t2 where activate_date < sch.timenow;
Oct 2 '08 #4
On Oct 2, 1:14*pm, Richard <rmcgor...@gmai l.comwrote:
That worked. *Thanks. :-)

A couple of follow-up questions:

How would I display the value stored in a variable? *In Sybase I could
"select" it, or "print" it. *The select gives me an error and I don't
see the print option in the "SQL Reference Volume 2" manual. *No other
commands in Volume 2 are popping out as obvious options. *I must be
missing it.

Why do I need to type

* * SELECT current date FROM sysibm.sysdummy 1

in my script, instead of

* * SELECT current date

to display the current date, yet in your example you can say

* *SET V_DATENOW = current date

Is it just a difference between the SET versus SELECT command?

Thanks.
Well, not to pick on Sybase, but--while handy--I don't believe a query
like 'SELECT current date' is valid, i.e. well-formed (ANSI), SQL, as
it's missing the FROM clause. Hence the need in DB2 to refer to the
dummy table.

Populating the variable is a different story, as that's in a
procedural context and the current date is coming from a special
register.

In a stored procedure you could also do a SELECT...INTO and populate a
variable using a SELECT, in addition to populating it via SET using a
scalar fullselect. In other words, in DB2, *depending on the setting*
you could do:

DECLARE V_TIMENOW TIMESTAMP;--
SET V_TIMENOW = CURRENT TIMESTAMP;--

--or--

DECLARE V_TIMENOW TIMESTAMP;--
SELECT CURRENT TIMESTAMP INTO V_TIMENOW FROM SYSIBM.SYSDUMMY 1;--

Come to think of it, you don't even need SYSIBM.SYSDUMMY 1, you also
could do something like:

SELECT CURRENT TIMESTAMP INTO V_TIMENOW FROM TABLE(VALUES(1) ) T(C1);--

--or--

DECLARE V_TIMENOW TIMESTAMP;--
SET V_TIMENOW = (SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY 1);--

--Jeff

Oct 2 '08 #5
DB2 9.5 provides global variables:
CREATE VARIABLE X INTEGER;
and
SET x = <some expression>;
To get them out from a client you can use:
SET ? = x;
(I don't think that works from CLP)

Insteda of SELECT x FROM SYSIBM.SYSDUMMY 1 you can use another ANSI
construct:
VALUES x;
Same numbers of characters to type as SELECT :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 10 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4921
by: Hal Vaughan | last post by:
First, I am aware of both SwingUtilities.invokeLater(), and of using Thread to create a new thread.  These are part of the problem. I want to have something running in the background, while the GUI is updating.  I've done that before without a problem, however, now, I need to pass variables to the separate Thread or Runnable that I'm using.  I'm using something like this: //Other code setting things up and updating GUI //Variables...
4
1618
by: Torsten Bronger | last post by:
Hallöchen! I have a file that looks a little bit like a C header file with a long list of variables (actually constants) definitions, e.g. VI_ATTR_TIMO = 0x54378 .... Actually I need this in a couple of low-level modules that are imported into the main module, and in the main module itself. They
1
4375
by: mark4asp | last post by:
What are the best methods for using global constants and variables? I've noticed that many people put all global constants in a file and include that file on every page. This is the best way of doing it - is it not? Once the application has loaded the page it is cached and is immediately available for other pages. With global variables - the best thing to do would be to use application variables - so long as there weren't too many...
5
3354
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example I'm adding a customer. The Customer fields are mostly foreign keys that refer to primary keys in other tables, left join instead of junction tables at this point. So, when I want to add a customer record, I also need to add records to the other...
7
3151
by: Michael | last post by:
Hi newsgroup, as the subject indicates I am looking for an advice using global variables. I am not if this problem is more about style then C. If its wrong in thi group, sorry. So I have a couple of function that all need the same information (all located in the same file). By now it looks like /* file beginns */
9
8674
by: CDMAPoster | last post by:
About a year ago there was a thread about the use of global variables in A97: http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/fedc837a5aeb6157 Best Practices by Kang Su Gatlin, casual mention was made about using static variables as an alternative to using global variables. This caused me to think of the following: '-----Begin module code
7
13717
by: misha | last post by:
Hello. I was wandering if someone could explain to me (or point to some manual) the process of mapping the addresses of host variables by DB2. Especially I would like to know when DB2 decides to reinitialize the addresses and even more when it decides not to do it. Recently I've ben strucked with a problem of host variables defined in LINKAGE SECTION, and it took me some time to find the cause and solution for the problem.
5
11838
by: Sandman | last post by:
I dont think I understand them. I've read the section on scope in the manual inside out. I'm running PHP 5.2.0 Here is the code I'm working on: //include_me.php <?php $MYVAR = array(); global $MYVAR, $a; ?>
1
29412
weaknessforcats
by: weaknessforcats | last post by:
C++: The Case Against Global Variables Summary This article explores the negative ramifications of using global variables. The use of global variables is such a problem that C++ architects have called it polluting the global namespace. This article explores what happens when the global namespace becomes polluted and how to avoid this condition. The opinions expressed in this article are those of the author alone although many have...
4
2386
by: icarus | last post by:
global_vars.py has the global variables set_var.py changes one of the values on the global variables (don't close it or terminate) get_var.py retrieves the recently value changed (triggered right after set_var.py above) Problem: get_var.py retrieves the old value, the built-in one but not the recently changed value in set_var.py. What am I doing wrong?
0
9903
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
9752
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
10692
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9525
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
7083
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5946
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4565
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
4167
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3193
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.