Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: table alias get lost on sub sub query?

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 06-29-11 at 01:35.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Interesting. I would have expected for this to work. There are no conflicts with the correlation name (=alias).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    Last edited by tonkuma; 06-29-11 at 05:39.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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)...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jun 2011
    Posts
    2

    That works! but still a bit surprise to me...

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •