Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

evgeniystuchalk
Contributor II

how to make 'where not exist' in mysql syntax?

Hello everyone! I'm trying to load data from mysql table with this condition:

FROM `amo.demo`.contacts where not exists (tsKey,`i_companyID`&'|'&`s_responsibleUser`&'|'&`i_contactID`&'|'&null());

And keep getting message about wrong syntax for mysql. What syntax is correct in this situation?

1 Solution

Accepted Solutions
evgeniystuchalk
Contributor II

Re: how to make 'where not exist' in mysql syntax?

My dumb, but working solution:

Load table from MySQL as temporary table, after that load data via Tesident command with requared limitations.

4 Replies

Re: how to make 'where not exist' in mysql syntax?

No idea. You'd have to ask someone specialized in the MySQL SQL dialect. The easy way out is to use a preceding load:

MyTable

LOAD * WHERE NOT Exists( ....etc... ) ;     // preceding load

SELECT * FROM `amo.demo`.contacts;     //  load data from mysql table


talk is cheap, supply exceeds demand
oknotsen
Honored Contributor III

Re: how to make 'where not exist' in mysql syntax?

What is it you are actually trying to exclude?

What you could do it add an Inline table to your script with just 1 field that contains a list of the values you do not want to include. Next, add "where not exists(fieldInInlineTable, fieldInAmo.DemoTable)". Drop the Inline table after that.

But based on what I think to understand from your example is that you first want to make some combination of field and exclude that combination. Maybe you can give us a more clear example?

May you live in interesting times!
MVP
MVP

Re: how to make 'where not exist' in mysql syntax?

The correct syntax usually requires that you use a subquery in exists(....), see for example

MySQL :: MySQL 5.7 Reference Manual :: 13.2.10.6 Subqueries with EXISTS or NOT EXISTS

But the correct syntax might not solve your requirements alone, i.e. you should be aware that exists() in QV and exists() in SQL are fundamental different besides their syntax.

Exists() in QV is checking if a field value loaded so far (up to the previous ouput table record, but including all previous field values loaded) is identical with in field value or an expression evaluated with the current input record.

So a key question () looking at your sample line would be, if the tsKey is present in your output table of this table load and if you would need to handle multiple occurences of composite key.

If in doubt or there is no real need to push load onto the DB server, go with Gysbert's suggested solution.

Regards,

Stefan

evgeniystuchalk
Contributor II

Re: how to make 'where not exist' in mysql syntax?

My dumb, but working solution:

Load table from MySQL as temporary table, after that load data via Tesident command with requared limitations.