Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    4

    Unanswered: Possible to have a subquery within a JOIN statement?

    so i was wondering if there's anyone who can help me sort something out.

    i have an SQL query that should show the details of a person (a search feature) that has a subquery inside an Inner Join, and the subquery is a Select statement.

    Currently there's no problem with the sql query, but when i test the query as db2 i have this error:

    An ON clause associated with a JOIN operator or in a MERGE statement is not valid.. SQLCODE=-338, SQLSTATE=42972, DRIVER=4.11.69

    So, I'm asking, are there some ways to rewrite the query without having to use a subquery within the join statement?

    I'm using DB2 For Linux, UNIX and Windows and v9.7

    enlighten me

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Show exact statement(or all of FROM clause) and all text of error message which you received.
    .

  3. #3
    Join Date
    May 2011
    Posts
    4
    basically, here's the query:

    SELECT DISTINCT p.id, p.lname, p.fname, e.no from table1 AS p
    INNER JOIN table2 as e ON e.no=(SELECT no FROM table3 AS f
    WHERE p.id=f.id AND f.isExit=0
    AND f.pstatus <> 'cancelled'
    AND f.status NOT IN ('deleted','hidden','inactive','void')
    ORDER BY f.edate DESC FETCH FIRST 1 ROWS ONLY))

    WHERE ...

    so the problem is, the select statement in the Inner Join Statement

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Join the subquery with a keyword LATERAL(TABLE can be specified in place of LATERAL.), like this...

    Code:
    SELECT DISTINCT
           p.id
         , p.lname
         , p.fname
         , e.no
     from  table1 AS p
     CROSS JOIN
           LATERAL
           (SELECT no
             FROM  table3 AS f 
             WHERE p.id = f.id
               AND f.isExit = 0 
               AND f.pstatus <> 'cancelled' 
               AND f.status NOT IN ('deleted','hidden','inactive','void') 
             ORDER BY
                   f.edate DESC
             FETCH FIRST 1 ROWS ONLY
           ) f
     INNER JOIN
           table2 as e
       ON  e.no = f.no
    
     WHERE ...
    Last edited by tonkuma; 05-10-11 at 06:00.

  5. #5
    Join Date
    May 2011
    Posts
    4
    thanks lots tonkuma you're a lifesaver

Posting Permissions

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