SQL except in QS

[waleeed_mahmood]

Hi all,

I have a question related to QS and SQL. I have seen that you can load values from a table using an EXCEPT expression in SQL. So, for example,

Load * from T1

EXCEPT

Load * from T2;

This will load all values from T1 except those values that exist in the T2. 

I would like to do the same in QS, if possible. I have two tables T1 and T2 in SQLMS i want to read in T1 except the rows T2 has, that have been marked as delete. I would like to keep the rows in my original table but simply not load those rows in QS. 

T1 has many columns but T2 has enough columns that can uniquely identify a row in T1.

Is it possible? if yes, could someone please provide a sample script?

Thank you.

[albertovarela · Accepted solution]

Take a look at the Exists function.  "Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement."

[crichter14]

I'm not sure I understand the example.  Do you use EXIST like a where clause and list out each field?

[albertovarela]

The function compares a field or an expression declaring to several fields in the current load, against a single field already loaded via the script.

If you'd like to compare a full record (several fields) you could create a concatenated field with all your fields (maybe hash or Autonumber),  and then use it with the Exists in the where clause.

Here's one example. I hope it helps