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

    Where not exists removes all records

    Steven Young

      Hi,

       

      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:

       

       

      table1:

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

       

       

      table2:

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