Executing recursive query in DB2 I received a SQL code -340

Question

I wrote the follow recursive query that gives me the list of IDs that I need and it is working just fine:

 with rec_table (emailid) as
 ((select email.id  as emailid from email, addressee, address, use, user 
   where email.id = addressee.EMAIL and addressee.to = address.ID 
   and address.id = use.ADDRESS and use.user = user.id and user.id = user_t(121))
 union
 (select email.id as emailid from email, address, use, user 
  where email.from = address.id 
  and address.ID = use.ADDRESS and use.user = user.id and user.id = user_t(121))

union all

select email.id as emailid from email, rec_table as x
 where email.contained_in = x.emailid)

select * from rec_table

However, if I need to do something more elaborated with the result of the query, like in the query below, I received an error

 with rec_table (emailid) as
 ((select email.id  as emailid from email, addressee, address, use, user 
   where email.id = addressee.EMAIL and addressee.to = address.ID 
   and address.id = use.ADDRESS and use.user = user.id and user.id = user_t(121))
 union
 (select email.id as emailid from email, address, use, user 
  where email.from = address.id 
  and address.ID = use.ADDRESS and use.user = user.id and user.id = user_t(121))

union all

select email.id as emailid from email, rec_table as x 
where email.contained_in = x.emailid)

select user.id, address.id from rec_table as x, addressee, address, use, user  
where  x.emailid = addressee.EMAIL and addressee.to = address.ID 
and address.id = use.ADDRESS and use.user = user.id

I received sql error code -340:

SQL Error [42726]: The common table expression "" has the same identifier as another occurrence of a common table expression definition within the same statement.. SQLCODE=-340, SQLSTATE=42726, DRIVER=4.16.53 SQL Error [56098]: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-340", SQLSTATE "42726" and message tokens "".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.16.53 The common table expression "" has the same identifier as another occurrence of a common table expression definition within the same statement.. SQLCODE=-340, SQLSTATE=42726, DRIVER=4.16.53

Any ideas?

mustaccio · Accepted Answer · 2014-05-15 14:26:29Z

Try moving the recursion initialization part into its own CTE:

with blah (emailid) as 
 ((select email.id  as emailid from email, addressee, address, use, user 
   where email.id = addressee.EMAIL and addressee.to = address.ID 
   and address.id = use.ADDRESS and use.user = user.id and user.id = user_t(121))
 union
 (select email.id as emailid from email, address, use, user 
  where email.from = address.id 
  and address.ID = use.ADDRESS and use.user = user.id and user.id = user_t(121))
),
rec_table (emailid) as (

  select emailid from blah
 union all
  select email.id as emailid from email, rec_table as x 
  where email.contained_in = x.emailid)

select user.id, address.id from rec_table as x, addressee, address, use, user  
where  x.emailid = addressee.EMAIL and addressee.to = address.ID 
and address.id = use.ADDRESS and use.user = user.id

NB: not tested.

Collectives™ on Stack Overflow

Executing recursive query in DB2 I received a SQL code -340

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related