table alias get lost on sub sub query?

didadidadidida · #1 (**permalink**) 06-28-11, 22:40

This works in oracle but not DB2:

select * from ss_unit a where exists (select 1 from (select * from ss_unit_authors b where a.UNIT_ID = b.unit_id ))

DB2 compaints a.UNIT_ID is undefined.

But this works for both oracle and DB2:

select * from ss_unit a where exists (select 1 from ss_unit_authors b where a.UNIT_ID = b.unit_id )

So DB2 would get table alias lost in nested sub query (DB2 sucks if so)?

Need help if there is a get around in DB for this.
Thanks in advance

tonkuma · #2 (**permalink**) 06-29-11, 00:27

Keyword LATERAL will solve your problem.
Note: TABLE can be specified in place of LATERAL.

Example:

Code:

------------------------------ Commands Entered ------------------------------
WITH
ss_unit(unit_id) AS (
VALUES
 (1) , (2) , (3)
)
, ss_unit_authors
(unit_id , author_nbr , author_name) AS (
VALUES
  (1 , 1 , 'Alice')
, (1 , 2 , 'Bob')
, (3 , 1 , 'Cyndi')
)
select *
 from  ss_unit a
 where exists
       (select 1
         from  LATERAL
               (select *
                 from  ss_unit_authors b
                 where a.UNIT_ID = b.unit_id
               )
       )
;
------------------------------------------------------------------------------

UNIT_ID    
-----------
          1
          3

  2 record(s) selected.

stolze · #3 (**permalink**) 06-29-11, 03:28

Interesting. I would have expected for this to work. There are no conflicts with the correlation name (=alias).

tonkuma · #4 (**permalink**) 06-29-11, 03:53

If LATERAL was not specified, it returned error.

Example: comment out LATERAL

Code:

------------------------------ Commands Entered ------------------------------
WITH
ss_unit(unit_id) AS (
VALUES
 (1) , (2) , (3)
)
, ss_unit_authors
(unit_id , author_nbr , author_name) AS (
VALUES
  (1 , 1 , 'Alice')
, (1 , 2 , 'Bob')
, (3 , 1 , 'Cyndi')
)
select *
 from  ss_unit a
 where exists
       (select 1
         from  -- LATERAL
               (select *
                 from  ss_unit_authors b
                 where a.UNIT_ID = b.unit_id
               )
       )
;
------------------------------------------------------------------------------
SQL0204N  "A.UNIT_ID" is an undefined name.  SQLSTATE=42704

tonkuma · #5 (**permalink**) 06-29-11, 04:35

LATERAL(TABLE on DB2 for z/OS) for nested-table-expression was documented in manual "SQL Reference" with examples.

Chapter 4.(or Chapter 5.) Queries( or SQL queries) ---> subselect ---> from clause ---> Correlated references in table-references

DB2 for LUW:
Example 3: and Example 6:

DB2 8 for z/OS:
Table 50. Examples of correlated references

DB2 10 for z/OS:
Table 84. Examples of correlated references

DB2 for i:
Example 1

stolze · #6 (**permalink**) 06-29-11, 05:39

Well, I'm wondering why the LATERAL or TABLE keyword should be needed at all. Is there a semantical reason?

I would have assumed that the correlation name "a" should be available in the deeper nesting as well without additional syntax. If I remove the (unnecessary) nesting in the SELECT 1 FROM (...) part, the query just runs fine:

Code:

$ db2 "select * from  ss_unit  a where exists (select * from  ss_unit_authors b where a.UNIT_ID = b.unit_id )"                                                                                                      

UNIT_ID   X
----------- ----------

0 record(s) selected.

The only difference is that the subselect is in the WHERE clause (as opposed to the FROM clause)...

didadidadidida · #7 (**permalink**) 06-29-11, 09:37

This might not be a bug for DB2 (since there is a workaround) but definitely there should be an enhancement for DB2 which could require quite some work for DB2 maker though.

Maybe there is some reason the DB2 does not support this like Oracle does?

Thanks for your big help!

Internet Services

Web Development

Digital Marketing

Consumer Tech