zOs/TX/PER01SWX
call sqlConnect dbaf
dv = '30.05.2011'
dt = '02.06.2011'
s ="with x (v, b, t) as" ,
"( select date('01.01.2010'), date('31.12.2010'), 'x0'",
"from sysibm.sysDummy1" ,
"union all select date('01.01.2011'), date('31.12.2011'), 'x1'",
"from sysibm.sysDummy1" ,
"union all select date('01.01.2012'), date('31.12.2012'), 'x2'",
"from sysibm.sysDummy1" ,
") , u as" ,
"( select * from gdb9998.tPer01x0 , x",
"where t = 'x0' and da between v and b",
"union all select * from gdb9998.tPer01x1 , x",
"where t = 'x1' and da between v and b",
"union all select * from gdb9998.tPer01x2, x",
"where t = 'x2' and da between v and b" ,
")",
"select count(*), min(ix), max(ix)",
"from u",
/* "where da >= ? and da <= ? ", */,
"where da between ? and ? ",
"and ix between 0530000000 and 0602999999"
q ="with u as" ,
"( select * from gdb9998.tPer01x0",
"where da between '01.01.2010' and '31.12.2010'",
"union all select * from gdb9998.tPer01x1",
"where da between '01.01.2011' and '31.12.2011'",
"union all select * from gdb9998.tPer01x2",
"where da between '01.01.2012' and '31.12.2012'",
")",
"select count(*), min(ix), max(ix)",
"from u",
/* "where da >= ? and da <= ? ", */,
"where da between ? and ? ",
"and ix between 0530000000 and 0602999999"
p1 = pos('?', s)
p2 = pos('?', s, p1+1)
if 0 then do
call sqlPreAllCl 1, left(s,p1-1) "'"dv"'" substr(s, p1+1, p2-p1-1),
"'"dt"'" substr(s, p2+1),
, st, ':cnt,:min,:max'
end
else do
call sqlPrepare 1, s
call sqlOpAllCl 1, st, ':cnt,:min,:max', dv, dt
end
$$- m.st.0 'cnt' cnt':' min '-' max
call sqlDisconnect
$#out 20100802 17:29:05
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:28:40
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:26:20
$#out 20100802 17:28:11
$#out 20100802 17:27:26
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:25:24
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:16:15
$#out 20100802 17:24:10
$#out 20100802 17:23:56
1 cnt 01.01.2011: 31.12.2011 - x1
$#out 20100802 17:08:27
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:07:21
$#out 20100802 17:07:59
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:06:34
$#out 20100802 17:07:06
$#out 20100802 17:06:48
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:06:18
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:05:57
1 cnt 8000: 530000000 - 602001999
$#out 20100802 17:03:54
1 cnt 8000: 530000000 - 602001999
$#out 20100802 16:39:07
$#out 20100802 17:03:10
$#out 20100802 17:02:22
$#out 20100802 16:59:51
$#out 20100802 16:58:48
$#out 20100802 16:57:08
$#out 20100802 16:56:27
$#out 20100802 16:54:47
$#out 20100802 16:54:26
$#out 20100802 16:54:05
$#out 20100802 16:51:27
$#out 20100802 16:39:48
$#out 20100802 16:39:19
1 cnt 8000: 530000000 - 602001999
$#out 20100802 16:37:23
$#out 20100802 16:38:39
1 cnt 8000: 530000000 - 602001999
$#out 20100802 16:30:50
$#out 20100802 16:36:20
$#out 20100802 16:35:21
$#out 20100802 16:33:53
$#out 20100802 16:33:23
$#out 20100802 16:31:08
1 cnt 8000: 530000000 - 602001999
$#out 20100802 16:26:57
1 cnt 8000: 530000000 - 602001999
$#out 20100802 16:13:27
1 cnt 8000: 530000000 - 602001999
$#out 20100802 16:13:14
$#out