Re: CTE CONVERSION TO DB2

[Serge Rielau]

Lennart wrote:

Apparently this restriction is no more:

[lelle@53dbd181 ~]$ db2 "with T0(n) as (values 1), T1(n,m) as (select
x.n, y.n from T0 x cross join T0 y) select * from T1"

N M
----------- -----------
1 1

Ignore my previous post

We added CROSS JOIN as a synonym for INNER JOIN ON 1=1 in DB2 9.5.
There are a pile of such "syntactic sugar" featurettes in DB2 9.5.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

On May 13, 4:27 pm, Serge Rielau <[e-mail]:

Lennart wrote:

Apparently this restriction is no more:

[lelle@53dbd181 ~]$ db2 "with T0(n) as (values 1), T1(n,m) as (select
x.n, y.n from T0 x cross join T0 y) select * from T1"

N M
----------- -----------
1 1

Ignore my previous post

We added CROSS JOIN as a synonym for INNER JOIN ON 1=1 in DB2 9.5.
There are a pile of such "syntactic sugar" featurettes in DB2 9.5.

Strange, I was pretty sure that you couldn't do:

with T0(n) as (values 1), T1(n,m) as (
select x.n, y.n from T0 x inner join T0 y on x.n = y.n
) select * from T1

in V8.2. I've verified that you can actually do it. Do you know when
this was allowed, 8.1 or 8.2 (I don't have a 8.1 installation to
test)? I'm pretty sure it was not allowed in V7. I'm just curious,
never had any need for anything but "," joins in CTE, hence never
tried it after V7.
/Lennart

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dave Hughes]

Lennart wrote:

Strange, I was pretty sure that you couldn't do:

with T0(n) as (values 1), T1(n,m) as (
select x.n, y.n from T0 x inner join T0 y on x.n = y.n
) select * from T1

in V8.2. I've verified that you can actually do it. Do you know when
this was allowed, 8.1 or 8.2 (I don't have a 8.1 installation to
test)? I'm pretty sure it was not allowed in V7. I'm just curious,
never had any need for anything but "," joins in CTE, hence never
tried it after V7.

I'm pretty sure that syntax was valid in v7. In fact, I think it might
have been valid as far back as v5 (I'm fairly sure CTEs and VALUES were
in v5, along with INNER JOIN et al).
Cheers,

Dave.

[Lennart]

On May 13, 6:32 pm, "Dave Hughes" <[e-mail]:

Lennart wrote:

Strange, I was pretty sure that you couldn't do:

with T0(n) as (values 1), T1(n,m) as (
select x.n, y.n from T0 x inner join T0 y on x.n = y.n
) select * from T1

in V8.2. I've verified that you can actually do it. Do you know when
this was allowed, 8.1 or 8.2 (I don't have a 8.1 installation to
test)? I'm pretty sure it was not allowed in V7. I'm just curious,
never had any need for anything but "," joins in CTE, hence never
tried it after V7.

I'm pretty sure that syntax was valid in v7. In fact, I think it might
have been valid as far back as v5 (I'm fairly sure CTEs and VALUES were
in v5, along with INNER JOIN et al).

Hmm, I realize blushing that I've probably mixed up the restrictions
on CTE and MQT refresh immediate. Sorry for any confusion I've caused
/Lennart

[Serge Rielau]

Lennart wrote:

Hmm, I realize blushing that I've probably mixed up the restrictions
on CTE and MQT refresh immediate. Sorry for any confusion I've caused

Actually I was about to point o RECURSIVE CTE restrictions.
There are some (overly severe) restrictions on join syntax in recursion.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Lennart]

On May 13, 8:29 pm, Serge Rielau <[e-mail]:

Lennart wrote:

Hmm, I realize blushing that I've probably mixed up the restrictions
on CTE and MQT refresh immediate. Sorry for any confusion I've caused

Actually I was about to point o RECURSIVE CTE restrictions.
There are some (overly severe) restrictions on join syntax in recursion.

Please do if you have it at hand. I remember vaguely that I started
thinking about what we could express with recursion in sql:

http://tinyurl.com/4fkdtn

but then my evil boss thought I had better things to do ... ;-)
/Lennart