3 Replies Latest reply on Jun 16, 2014 10:19 AM by Steven Hawkins

    More DB2 mainframe weirdness - LIMIT within UNION

    Mark Addleman Master

      Related to our earlier discovery on DB2 mainframe, it seems that LIMIT is not allowed within a particular SELECT in a UNION.  For example, the following is illegal in DB2 :

      SELECT * FROM (

      SELECT * FROM T1 LIMIT 100

      UNION ALL
      SELECT * FROM T2 LIMIT 100

      ) LIMIT 100

       

      This is problematic because we have a Teiid view that is defined with a UNION ALL.  The Teiid planner will push the LIMIT to each of the SELECTs wtihin the view causing DB2 to barf.  I can get around this problem by turning off supports for union.