Where not exists removes all records

[steveniow]

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);

[Gysbert_Wassenaar · Accepted solution]

It looks like RTTEPIPERIOD2.qvd does not contain any records with PathwayNumber values that do not exist in RTTEPIPERIOD.qvd

[steveniow]

thank you.

it wasn't quite that - I had checked - but in going back I spotted other bits!

still my own stupidity though!