CODEWITHSUNDEEP
loopsdb2
RSqI93
RSqI93

Reputation: 55

Is there a way to add a while loop in DB2?

I currently have a query set up through Unions [code block 1] to gather data. It works properly, but very inefficient to have it repeat that many times

Is there a way to convert this to a while loop in DB2?

I've tried to write a while loop in [Code Block 2] but it doesn't work in DB2.

I keep getting a syntax error in Code_block_2

In code block 2, I want to add 1 to the start and end date variables so the next query will run with new time periods (stored as integers) eg 1st time will be between 201601 and 201701, the second time will be between 201602 and 201702. I tried to set the end_dt to print with each row of data

The union method works, but I have to write the same chunk of code a bunch of times. A loop would be more effective

[code block 1 - Union (Works)]

select
  1 as a.period,
  a.name,
  a.date
from
  data_table
where
  month between 201601 and 201701
union
select
  1 as period,
  a.name,
  a.date
from
  data_table a
where
  a.month between 201602 and 201702 -- this is changing in a consistent pattern
union
select
  1 as period,
  a.name,
  a.date
from
  data_table a
where
  a.month between 201603 and 201703

[Code block 2 - with loop attempt]

Begin atomic
declare @end_dt integer default 201701;
declare @start_dt integer default 201601;
while @end_dt < 201712  do 
   --statement
   select
     @end_dt as period,
     a.name,
     a.date
   from
     data_table a
   where
      month between @start_dt and @end_dt;
-- add + 1 to variables
set @end_dt = @end_dt +1;
set @start_dt = @start_dt +1;
end while;
end;

here is an example data set

(In data base)

name - date Jane - 5/2/2016
Jim - 6/3/2016
Zack - 1/1/2016
Jill - 5/1/2016
Joe - 1/1/2016
James - 4/1/2016
Zoe - 2/2/2016

Output (Expected, and result of code block 1.)

Period - Name - date
201701 - Jane - 5/2/2016
201701 - Jim - 6/3/2016
201701 - Zack - 1/1/2016
201701 - Jill - 5/1/2016
201701 - Joe - 1/1/2016
201701 - James - 4/1/2016
201701 - Zoe - 2/2/2016
201702 - Jane - 5/2/2016
201702 - Jim - 6/3/2016
201702 - Jill - 5/1/2016
201702 - James - 4/1/2016
201702 - Zoe - 2/2/2016

-- notice how the 1/1/2016 dates trickle off the set in 201702 period
I'm not 100% sure on the version of DB2, But it's consistent with DB2 for Linux Unix and Windows 10.5 and z/OS

Upvotes: 0

Views: 4802

Answers (2)

Michael S.
Michael S.

Reputation: 146

Do you have a date dim / calendar table on the system?

If not, I just put in a values table. This can be changed to whatever you need. It just needs to have a list of YYYYMM date values over the period you want. 

select
  dates.period,
  a.name,
  a.date
from
  table( values 
         (201701)
,(201702)
,(201703)
,(201704)
,(201705)
,(201706)
,(201707)
,(201708)
,(201709)
,(201710)
,(201711)
,(201712)
) date(period)
join
  data_table
    on a.month between (date.period - 100) and date.period

Upvotes: 0

Mark Barinstein
Mark Barinstein

Reputation: 12314

Run the following query as is.
Is this a "virtual" table range you are looking for?

with t(n, m) as (
select 1, date(to_date(201601, 'YYYYMM')) from sysibm.sysdummy1
  union all
select n+1, m + 1 month
from t 
where n<11
)
, range (start, end) as (
select year(m)*100+month(m) as start, year(m + 1 year)*100+month(m + 1 year) as end
from t
)
select start, end
from range r;

If yes, then you can join this range table with your data_table on the a.month between r.start and r.end condition to get the result you provided.

Upvotes: 1

Related Questions