Topic
2 replies Latest Post - ‏2012-06-21T22:54:44Z by Peter.S
Kenneth_IL
Kenneth_IL
1 Post
ACCEPTED ANSWER

Pinned topic UNION ALL query returns no ROWS

‏2012-03-30T14:52:43Z |
Consider the following java code snippet:


String sql = 
"SELECT ITEMID FROM " + 
"(SELECT ITEMID, CREATETS, LASTCHANGEDTS FROM ICMADMIN.LLC_MISC1001 " + 
"UNION ALL " + 
"SELECT ITEMID, CREATETS, LASTCHANGEDTS FROM ICMADMIN.LLC001) AS TBL1 " + 
"WHERE DATE(CREATETS) <= '2012-02-28' AND DATE(LASTCHANGEDTS) <= '2012-02-28'"; PreparedStatement pstate = connection.prepareStatement(sql); Results resultset1 = pstate.executeQuery();

When I execute this code againt a 8.2 UDB database there are no exceptions but the query does not return any rows. I tried both the type 2 and type 4 drivers. Each time ResutlSet.next() fails.
When I execute this query from the DB2 Control Center it returns the rows as expected.
Updated on 2012-06-21T22:54:44Z at 2012-06-21T22:54:44Z by Peter.S
  • SystemAdmin
    SystemAdmin
    1731 Posts
    ACCEPTED ANSWER

    Re: UNION ALL query returns no ROWS

    ‏2012-03-30T18:38:11Z  in response to Kenneth_IL
    How are you processing the ResultSet?
  • Peter.S
    Peter.S
    1 Post
    ACCEPTED ANSWER

    Re: UNION ALL query returns no ROWS

    ‏2012-06-21T22:54:44Z  in response to Kenneth_IL
    You look like yor are missing a ) at the end of the SQL AND DATE(LASTCHANGEDTS) <= '2012-02-28'*)*";
    String sql = "SELECT ITEMID FROM " +
    "(SELECT ITEMID, CREATETS, LASTCHANGEDTS FROM ICMADMIN.LLC_MISC1001 " +
    "UNION ALL " +
    "SELECT ITEMID, CREATETS, LASTCHANGEDTS FROM ICMADMIN.LLC001) AS TBL1 " +
    "WHERE DATE(CREATETS) <= '2012-02-28' AND DATE(LASTCHANGEDTS) <= '2012-02-28'";
    PreparedStatement pstate = connection.prepareStatement(sql);
    Results resultset1 = pstate.executeQuery();