Find your content:

Search form

You are here

Query for what's not found

 
Share

I have a parsed csv where I need to match on two columns:

for (integer i=1; i<lines.size()+1; i++){
            try{
                PremiseNumbers.add(lines[i][Pindexnumber]);
                AccountNumbers.add(lines[i][Aindexnumber]);
            }catch(exception e){
            }
        }

Then I take those two lists and do this query:

                premlist = [select id, Account_Number__c, Gas_Premise_Number__c, balloted__c from  premises__c where Gas_Premise_Number__c in: PremiseNumbers and Account_Number__c in: AccountNumbers];        

What's a good way to get the records that weren't found by the query but are in the list?

Bonus point for minimizing script statements since parsing a CSV eats them up.


Attribution to: Shane McLaughlin

Possible Suggestion/Solution #1

I can think of going over premlist, and for each record that I find, mark on the original list (or a duplicate of it) a true/false marker.

Once you are over the list, you will have a true/false list of what was found.

I definitely don't get the mark for optimization - but as I told my LISP and Compilers professor: If it works - I don't touch it. ((((LISP))))


Attribution to: Saariko

Possible Suggestion/Solution #2

For starters, I'm hoping PremiseNumbers and AccountNumbers are both sets, so that only unique ids are collected

Set<String> premiseAcocuntIds = new Set<String>.addAll(PremiseNumbers);
premiseAccountIds.addAll(AccountNumbers);

for (Premises__c prem : [select id, Account_Number__c, Gas_Premise_Number__c, balloted__c from  premises__c where Gas_Premise_Number__c in: PremiseNumbers and Account_Number__c in: AccountNumbers])   {
premiseAAccountIds.remove(prem.Gas_Premise_Number__c);
premiseAAccountIds.remove(prem.Account_Number__c);
}

What will be left over in the premiseAccountNumbers set is the ones that didnt have a match.


Attribution to: techtrekker
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/3463

My Block Status

My Block Content