How to exclude rows from the results of a select

[#1]

I'm wanting to load data from a select statement where the emplid is not in a loaded table from a spread sheet. In other words, I load a spread sheet of EMPLID's and then I want to load the records from the HR database only for those EMPLID's that were on the spread sheet. I can't figure out if I can do a load of the select statement with a where clause and then use a sub select for where the emplid exists in the QVD file, or something like that. Any Ideas?

[rbecher · Accepted solution]

Just do this:

1. load EMPLID from <Excel sheet>

2. load
EMPLID, ...
from <HR database>
WHERE exists(EMPLID);

- Ralf

[rbecher]

Maybe I got it wrong in the first place. But you could try this:

2. load
EMPLID, ...
WHERE exists(EMPLID);
select * from <HR database>;

I never tried this, would be interesting if this works...

- Ralf

[#4]

The syntax was accepted, however, as far as I can tell, it did not reduce the data. It still pulled in 6000 rows rather than 400 rows.

[rbecher]

It should work if the field name EMPLID is the same in both loads. Can you upload your script code?

Ralf

[Anonymous]

I've been using this method all the time. It works and it is reliable. Something is missing in the information provided.

[#7]

I found my problem. I had another select on another tab that also was using the EMPLID, pulling in all of the records. By using the

WHERE exists(EMPLID) on this load, and on the load I was working with, it resolved the problem.

Thanks for you help!

[#8]

Hello,

I've been using the text

Where not [Projection Period] like '0';

This has allowed me to exclude all the Projection periods which have a 0 for the value. Now I need to exclude another value from another field. I initially tried

Where not [Projection Period] OR [Scenario] like '0' OR 'X';

Where X represents the scenario I wish to exclude and while it works it also slowed the load statement to a crawl. I'm sure there is a way to split these up and I just don't know how. Could you offer any advice?

Many Thanks