2 Replies Latest reply: Jul 4, 2016 7:36 AM by Steven Young RSS

    Where not exists removes all records

    Steven Young



      I have two tables, one of which contains a Key (PathwayNumber) that I need to remove based on its existence in the other. the data against each key is different albeit that the Key is the same.

      I have tried a couple of variations but most recently the script looks like below. Without the Not Exists I have just over 5000 records in table1 and a couple of million in table 2. With the statement, table 2 is empty.

      Can anyone tell me what I am doing wrong?


      The script looks like this:




      LOAD InternalPatientNumber as IntNo,
      EpisodeNumber as EpiNo,
      PathwayNumber & '|' & PPeriodId as PNkey,
      as Inpatient,
      PathwayNumber as PathwayTemp
      FROM inputpathway\RTTEPIPERIOD.qvd] (qvd);




      LOAD InternalPatientNumber as INTERNALPATIENTNUMBER
      EpisodeNumber as EPISODENUMBER
      PathwayNumber & '|' & PPeriodId as PNkey
      FROM [ inputpathway\RTTEPIPERIOD2.qvd] (qvd)
      Where not exists(PathwayTemp,PathwayNumber);