I am facing a challenge trying to understand DB2 sql (note, I am coming from MS SQL Server) :P.
Here is a scenario, I have got 2 tables one has IDs and other details, second one has lot of other info corresponding to each ID.
ID Info for ID
_______ ____> _______
| | / | |
| T1 |<---------> | T2 |
|_____| \____> |_____|
Coming from SQL Server, I am used to running scripts like:
Declare @ID int
Declare @ID1 int
select @ID=ID from T1 where col1 = @ID1
select * from T2 where ID = @ID
This all runs fine there and gives me an ID corresponding ID1 which can further be used to obtain all info about ID from T2.
Sadly, in DB2 this explodes right in my face, I am scared if I will execute this query one more time, it will disown me forever :(.
I did some research and wrote this (am even stuck at variable declaration).
--#SET TERMINATOR @
BEGIN ATOMIC
DECLARE UID char(30);
END @
For others it worked great, but I am getting following error:
BEGIN ATOMIC
DECLARE UID char(30);
END
ILLEGAL USE OF KEYWORD ATOMIC. TOKEN WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=3.63.108
Other Info:
IBM DB2 for z/OS V9.1 IBM Data Studio V3.1.1.0
[EDIT: using DECLARE] Another thing I tried that did not work for:
CREATE VARIABLE UID CHAR(30) DEFAULT 'USERID';
select * from testdb2.T1 A WHERE A.UID=v_UID;
--some other activity goes here
--and here
DROP VARIABLE UID;
TIA, Abhinav
Update on May 13th, 2016 (Friday the 13th)
Creating a stored proc is the only way of fixing this :(
Here is the basic syntax for your first case:
In this example, however, you are trying to use a variable as a column name:
Unfortunately, you can't do that in DB2. The only way to do something like that is to build a dynamic sql statement and execute it. This is kind of a mess: you create an sql command in a string, and then prepare and execute it. And there are also restrictions on
SELECT
being used directly in dynamic sql. It is probably better to think of another design that will solve your problem, rather than going down this route.