Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a query that runs like a dog, mainly due to a where condition similar to this
AND c.uidcot not in (select z.uidcot FROM table1 Inner join table2 inner join table 3 where a=b and b=c and d=f) you get the point
So my plan was to move that nested select statement to it's own LOAD, and then in another LOAD bring in the core data, but place a WHERE NOT EXISTS (uidcot).
ExcludeUID:
LOAD uidcot;
SQL SELECT....;
Data:
LOAD
uidcot,
field1,
field2
WHERE NOT EXISTS (uidcot);
SQL SELECT .....;
Now this worked, but with a hitch.. What I only just learnt is that the WHERE NOT EXISTS checks Data as well.. It makes sense, but it somehow never occured to me that it would do that.
So, what I need to do is force the WHERE NOT EXISTS to only compare against the ExcludeUID table..
How is that done..
you need to use the second form of exists - comparing an expression to a field. Rename the other field, and compare one field to another:
ExcludeUID:
LOAD uidcot as uidcot_excl;
SQL SELECT....;
Data:
LOAD
uidcot,
field1,
field2
WHERE NOT EXISTS (uidcot_excl, uidcot);
SQL SELECT .....;
you need to use the second form of exists - comparing an expression to a field. Rename the other field, and compare one field to another:
ExcludeUID:
LOAD uidcot as uidcot_excl;
SQL SELECT....;
Data:
LOAD
uidcot,
field1,
field2
WHERE NOT EXISTS (uidcot_excl, uidcot);
SQL SELECT .....;