2 Replies Latest reply on Nov 11, 2014 7:16 AM by Rakesh Balguri

    Join SQL not working in Teiid 8.1 and working in Teiid 8.7

    Rakesh Balguri Expert

      Hi all,


      I am having the following SQL which doesn't return any data in Teiid 8.1 and this works fine in Teiid 8.7. The data sources are same and the difference is just the teiid version.

      SELECT CHORUS_B.evtsysid, CHORUS_B.evtutctod, CHORUS_B.datamart_name, CHORUS_B.evtsystod, CHORUS_B.evtuserid, CHORUS_J0.name AS "USEROFSYSACCESS@@@@NAME", CHORUS_B.evtjobname, CHORUS_B.evttypedesc, CHORUS_B.evtusername, CHORUS_B.evtcatdesc, CHORUS_B.vio1code, CHORUS_B.evtesmcode, CHORUS_B.vio2code, CHORUS_B.usrfacility, 'SECDM.VIEWSYSACCESS' AS "__objecttype__" FROM (SECDM.VIEWSYSACCESS AS CHORUS_B JOIN SECURITY.CMXREF AS cx2 ON CHORUS_B.evtsysid = cx2.lpar) JOIN SECURITY_USERUPDATE.BASEUSER AS CHORUS_J0 ON CHORUS_J0.sysid = cx2.sysid AND CHORUS_J0.userid = RTRIM(CHORUS_B.evtuserid)


      We are using Teiid 8.1 for an earlier version of our product and the customer has reported an issue against that version. This SQL doesn't return any data in Teiid 8.1.

      Attached are the Query Plans for Teiid 8.1 and Teiid 8.7.


      Is there a way to find out the patches that were applied on top of Teiid 8.1 to fix the issue? We would like to apply the patches on 8.1 and see if that fixes the issue.

      If not is there a workaround to have this SQL return data as in Teiid 8.1?


      I understand that there would be many changes between the two releases and we need your help in fixing this issue.

      Your help is much appreciated.