6 Replies Latest reply: Jan 28, 2014 9:40 AM by Senthil Raja RSS

    Concatenate with Where not Exists() is not concatentating records from second table

      Hi all

       

      I am concatenating the following two tables

       

      tblSurveyMeasure:

      LOAD
      SYS_ID
      ,
      Max(If(IsNull(DATE_COMPLETED), Floor(PLANNED), Floor(DATE_COMPLETED))) AS TechValDate
      Resident
      tblMeasure
      Where
      VOID = 'N' and WORK_CLASS = 'BS' and WildMatch(Lower(MEASURE), '*survey*') = 1 and (not IsNull(DATE_COMPLETED) or not IsNull(PLANNED))
      Group By
      SYS_ID;

       

      tblTechValStatus:

      NoConcatenate
      LOAD
      SYS_ID
      ,
      Max(Date(Floor(DATETIME))) AS TechValDate
      Resident
      tblLogStatus
      Where
      Match(Lower(SOP_AFTER), 'tech validation') = 1
      Group By
      SYS_ID;

      Concatenate (tblSurveyMeasure)
      LOAD
      SYS_ID
      ,
      TechValDate
      Resident
      tblTechValStatus
      Where
      not Exists(SYS_ID);

       

      Having tested the two tables independently I know that there are SYS_IDs present in the second table (tblTechValStatus) that are not present in the first table (tblSurveyMeasure), yet no records from the second table are concatenated.

       

      When I remove the Where not Exists clause then all records from the second table are concatenated, which leads me to believe that it is the Where clause that is causing the problem.

       

      Can anyone give any pointers on how the Exists function works.

       

      Thank you.