470,596 Members | 1,620 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

SQL and variables.

Hi All,

I am a pretty new DB2 user(Old MSSQL user), and therefor i have a question
to a query.

Usualy I can make stuff like (Silly example)

declare @hat int
set @hat = 10
select * from tblCustomer where custid = @hat

This would make the database return the record in where custid is 10.

How would the db2 version be?

An other question, I need to make a context depending insert, where the
logic is

if (select value from tblOptions where optionid = 'optionid' == 'T1') then
insert into t1(c1,c2,c3,c4) values (1,2,3,4)
else
insert into t2(c1,c2,c3,c4) values (1,2,3,4)

Is it possible to implement the above in one SQL call? Without using
stored procedures. (This because of strange dbo)

Regrads,
Morten
Aug 3 '06 #1
5 18143
Morten W. Nissen wrote:
Hi All,

I am a pretty new DB2 user(Old MSSQL user), and therefor i have a question
to a query.

Usualy I can make stuff like (Silly example)

declare @hat int
set @hat = 10
select * from tblCustomer where custid = @hat

This would make the database return the record in where custid is 10.

How would the db2 version be?
You can use variables (without the strange '@' notation) in DB2. However,
variables are not global so you are restricted to the scope of a single
statement. But this is not a real problem. Simply take the above sample
and wrap it into a dynamic compound statement:

BEGIN ATOMIC
DECLARE hat INTEGER;
SET hat = 10;
INSERT INTO tab(col) VALUES (hat);
END
An other question, I need to make a context depending insert, where the
logic is

if (select value from tblOptions where optionid = 'optionid' == 'T1') then
Just write this as standardized SQL:

IF ( SELECT value FROM tblOptions WHERE optionid = 'optionid' ) = 'T1' THEN
insert into t1(c1,c2,c3,c4) values (1,2,3,4)
else
insert into t2(c1,c2,c3,c4) values (1,2,3,4)
END IF

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 3 '06 #2
On Thu, 03 Aug 2006 18:51:56 +0200, Knut Stolze <st****@de.ibm.comwrote:
>
You can use variables (without the strange '@' notation) in DB2. BEGIN
ATOMIC
DECLARE hat INTEGER;
SET hat = 10;
INSERT INTO tab(col) VALUES (hat);
END
This I understand, and thank you alot, but if I do a

BEGIN ATOMIC
DECLARE hat INTEGER;
SET hat = 6;
select * from CUSTOMER where CUSTOMER.CUSTNO = hat;
END

The database returns nothing, (0 record(s) affected)

But if I do a
select * from CUSTOMER where CUSTOMER.CUSTNO = 6

It returns on nice little record. (CUSTOMER.CUSTNO is a Integer)

Is there a logic in that?

<snip>
IF ( SELECT value FROM tblOptions WHERE optionid = 'optionid' ) = 'T1'
THEN
>insert into t1(c1,c2,c3,c4) values (1,2,3,4)
else
insert into t2(c1,c2,c3,c4) values (1,2,3,4)

END IF
What if the database returns more then one value? Does it then compaire
only with the first one? Or?

Thank you very much for your help.

../Morten

A small followup, what online resource about db2 can be recommended?
Aug 3 '06 #3
Morten W. Nissen wrote:
Hi All,

I am a pretty new DB2 user(Old MSSQL user), and therefor i have a
question to a query.

Usualy I can make stuff like (Silly example)

declare @hat int
set @hat = 10
select * from tblCustomer where custid = @hat

This would make the database return the record in where custid is 10.

How would the db2 version be?
--#SET TERMINATOR @
BEGIN ATOMIC
declare hat int;
set hat = 10;
select * from tblCustomer where custid = hat;
END
--#SET TERMINATOR ;

Note that BEGIN ATOMIC is not meant to be the answer fro all scripting.
It can't do error handling or return result sets.
As things get more complex use a procedure or a procedural language like
Ruby, PERL, etc, etc...
>
An other question, I need to make a context depending insert, where the
logic is

if (select value from tblOptions where optionid = 'optionid' == 'T1') then
insert into t1(c1,c2,c3,c4) values (1,2,3,4)
else
insert into t2(c1,c2,c3,c4) values (1,2,3,4)
--#SET TERMINATOR @
BEGIN ATOMIC
if (select value from tblOptions where optionid = 'optionid' == 'T1') then
insert into t1(c1,c2,c3,c4) values (1,2,3,4);
else
insert into t2(c1,c2,c3,c4) values (1,2,3,4);
end if@
--#SET TERMINATOR ;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 3 '06 #4
Morten W. Nissen wrote:
This I understand, and thank you alot, but if I do a

BEGIN ATOMIC
DECLARE hat INTEGER;
SET hat = 6;
select * from CUSTOMER where CUSTOMER.CUSTNO = hat;
END
You're right. The result of the SELECT is not returned but rather consumed
at the end of the compound statement. I don't know how you could handle
queries that way.

What you could do is to wrap this stuff into a procedure. Another
alternative is to stick with the 'old' SQL approach and avoid procedural
logic.
<snip>
>IF ( SELECT value FROM tblOptions WHERE optionid = 'optionid' ) = 'T1'
THEN
>>insert into t1(c1,c2,c3,c4) values (1,2,3,4)
else
insert into t2(c1,c2,c3,c4) values (1,2,3,4)

END IF

What if the database returns more then one value? Does it then compaire
only with the first one? Or?
It gives you an error. The query _must_ be a scalar subselect. So if there
is the potential for multiple rows being returned, add a FETCH FIRST 1 ROW
ONLY clause (or some other mechanism to filter out only a single row.
A small followup, what online resource about db2 can be recommended?
I'm usually using the DB2 manuals:
http://publib.boulder.ibm.com/infoce...w/v8/index.jsp

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 4 '06 #5
If you want to see the result. How about this?

DECLARE GLOBAL TEMPORARY TABLE tblCustomer_temp
LIKE tblCustomer ON COMMIT PRESERVE ROWS;
--#SET TERMINATOR @
BEGIN ATOMIC
declare hat int;
set hat = 10;
INSERT INTO SESSION.tblCustomer_temp
select * from tblCustomer where custid = hat;
END@
--#SET TERMINATOR ;
select * from SESSION.tblCustomer_temp;

Aug 5 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Torsten Bronger | last post: by
7 posts views Thread by Michael | last post: by
7 posts views Thread by misha | last post: by
5 posts views Thread by Sandman | last post: by
4 posts views Thread by icarus | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.