topic Re: SQL except in QS in App Development

SQL except in QS

waleeed_mahmood — Sat, 16 Nov 2024 18:34:38 GMT

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.

Re: SQL except in QS

albertovarela — Thu, 28 May 2020 16:35:07 GMT

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."

Re: SQL except in QS

crichter14 — Wed, 04 Nov 2020 21:41:15 GMT

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

Re: SQL except in QS

albertovarela — Thu, 05 Nov 2020 18:35:12 GMT

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