1 2 Previous Next 24 Replies Latest reply on Dec 16, 2014 7:12 AM by gadeyne.bram

    Using Left Join returns wrong data in Teiid 8.7

    Rakesh Balguri Expert

      Hi

       

      When I execute the following SQL I get wrong data from teiid. If I use the OPTION MAKEDEP the SQL returns the correct data.

      My understanding is that the "OPTION MAKEDEP" is optional and is used only to improve performance correct?

      Why is there a difference in the results returned when MAKEDEP is not used and when MAKEDEP is used?

       

      Attached is the Query plan for With MakeDep and without MakeDep

       

      SELECT CHORUS_B."EVTSYSID", CHORUS_B."DATAMART_NAME", 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 

      left join SECURITY.CMXREF cx2 on CHORUS_B.evtsysid = cx2.lpar 

      left join SECURITY_USERUPDATE.BASEUSER CHORUS_J0 on CHORUS_J0.sysid=cx2.sysid and CHORUS_J0.userid = RTRIM(CHORUS_B.evtuserid)

       

      Thanks

      Rakesh

        1 2 Previous Next