function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
amyer2240amyer2240 

SOQL query returning 0 results

I have a trigger that isn't working, and I think it's not working because of the SOQL query below. I'm creating a new record in sandbox that matches the criteria in the query and troubleshooting in the debug log - does anyone see any reason this might not work? Thank you!

List<Opportunity> opportunities = new List<Opportunity>();
Map<Id, Opportunity> opportunitiesById = new Map<Id, Opportunity>(opportunities);
Set<Id> oppsset = new Set<Id>();
oppsset = opportunitiesbyId.keySet();
        
//get the opps and their relshps
cv__Relationship__c[] relationships = 
   [select Id, cv__UniqueId__c, cv__Opportunity__r.Id,  cv__Posted__c, cv__Reversed__c, cv__Posted_Date__c
   from cv__Relationship__c 
   where cv__UniqueId__c != null and cv__Reversed__c = false and cv__Posted__c = true and cv__Opportunity__r.Id in :oppsset and cv__Posted_Date__c < :Date.valueOf('2011-01-01')];

My debug log looks like:
SYSTEM_CONSTRUCTOR_ENTRY [3]|<init>()
SYSTEM_CONSTRUCTOR_EXIT [3]|<init>()
SYSTEM_CONSTRUCTOR_ENTRY [5]|<init>(Integer)
SYSTEM_CONSTRUCTOR_EXIT [5]|<init>(Integer)
SYSTEM_METHOD_ENTRY [6]|MAP<Id,Opportunity>.keySet()
SYSTEM_METHOD_EXIT [6]|MAP<Id,Opportunity>.keySet()
SYSTEM_METHOD_ENTRY [9]|com.salesforce.api.interop.apex.bd.DateMethods.ValueOf(String)
SYSTEM_METHOD_EXIT [9]|com.salesforce.api.interop.apex.bd.DateMethods.ValueOf(String)
SOQL_EXECUTE_BEGIN [9]|Aggregations:0|select Id, cv__UniqueId__c, cv__Opportunity__r.Id, cv__Posted__c, cv__Reversed__c, cv__Posted_Date__c from cv__Relationship__c where (cv__UniqueId != null and cv__Reversed__c = false and cv__Posted__c = true and cv__Opportunity__r.Id = :tmpVar1 and cv__Posted_Date__c < :tmpVar2)
SOQL_EXECUTE_END [9]|Rows:0

Vinit_KumarVinit_Kumar
Yes,that's right your query is returning 0 rows.It could be because the record is not fulfilling the where filter condtion.So,you need to check all the condition should return true on the record ,then only the query would return the record.So,

cv__UniqueId !=null,
cv__Reversed__c = false and all other cvonditions.

Hope this helps !!
amyer2240amyer2240
Thanks for the quick reply. I'm creating an opportunity and related relationship record that do fulfill all of those criteria and on which the trigger should be working - I've tried selectively removing criteria from the where statement and running it to try to isolate which statement is causing problems, but none appear to be the solution (except the in :oppsset, which gave me a too-large query error).

Does the Map/List/Set format look ok?

Thank you!
Vinit_KumarVinit_Kumar
This code is bit confusing to me.Where is Trigger.new or Trigger.old in your code ??
amyer2240amyer2240
There's more to the code than just the snippet above, but I think that the above is where the problem lies - I use the results of the SOQL query to manipulate the data later on, and when there's no rows resulting I can't manipulate anything. The code above is the first few lines of my trigger. 
Vinit_KumarVinit_Kumar
So,that's clear then you need to see why this query is not returning rows as there might be some conditions not being met.You need to get that part correct !!
amyer2240amyer2240
I'm creating an opportunity and related relationship in the sandbox that do fit these criteria - uniqueId is not null, reversed is false, posted is true, and posted date is 2010-01-01 - but the trigger is still not querying any rows. Could it be anything else?
Thank you!
Vinit_KumarVinit_Kumar
Can you try running the query in some SOQL tool like workbench and SOQL explorer by  hardcoding the Opportunity ids and date and see if you are getting the reults there ??
amyer2240amyer2240
Good idea!! I got "Unknown error parsing query" - any idea what that means? I got the error when Ids were hardcoded, when the date was hardcoded, and when I pasted the SOQL query as is (not hard-coded).

Thank you!!
Vinit_KumarVinit_Kumar
Can you post the query wwhich you are trying in SOQL tool with the hard coded values ??
amyer2240amyer2240
Eventually, this query (with hardcoded date) worked:

select Id, cv__UniqueId__c, cv__Opportunity__r.Id,  cv__Posted__c, cv__Reversed__c, cv__Posted_Date__c
   from cv__Relationship__c
   where cv__UniqueId__c != null and cv__Reversed__c = false and cv__Posted__c = true and cv__Posted_Date__c = 2012-11-06T00:00:00Z

I tried to hardcode the date in my trigger, which now looks like:
[select Id, cv__UniqueId__c, cv__Opportunity__r.Id,  cv__Posted__c, cv__Reversed__c, cv__Posted_Date__c
   from cv__Relationship__c 
   where cv__UniqueId__c != null and cv__Reversed__c = false and cv__Posted__c = true and cv__Opportunity__r.Id in :oppsset and cv__Posted_Date__c < :Date.valueOf('2011-01-01T00:00:00Z')];
but is still returning 0 rows. Any ideas?

Thank you!!
Vinit_KumarVinit_Kumar
In your query inside Trigger,I see another filetr condition which is cv__Opportunity__r.Id in :oppsset which I don't see in the hard coded one.

So,that's the issue here you need to see what value is coming there try debugging using System.debug() method and see the id what's there and use that id to run in the SOQL toll and see if you are getting any results.

I am sure you won't.

Hope this helps !!
amyer2240amyer2240
Brilliant, thank you! So I think the problem is with the oppsset list - if I do a System.debug([select Id from Opportunity where Id in :oppsset]) I get 0 results!

Do you see anything wrong with the map/list combo? Thank you again for your help!
amyer2240amyer2240
I feel like I'm going crazy! I typed this into the Execute Anonymous window in the dev console:
Opportunity[] opps = new Opportunity[]{};
        
System.debug([select Id from Opportunity where Id in :opps]);

And got zero results. What?? Any help is apprecaited! Thank you!
Vinit_KumarVinit_Kumar
You didn't populate the list. 
Opportunity[] opps = new Opportunity[]{'<Id of oppurtunity needs to be here>'};
        
System.debug([select Id from Opportunity where Id in :opps]);
amyer2240amyer2240
Thank you so much!!