+ Start a Discussion
King KooKing Koo 

Question about "ALL ROWS" in SOQL

Hi there

 

It seems to me that the "ALL ROWS" keyword that you use in SOQL (to access records in the recycled bin) can only be used in Apex.

 

I went to Developer Console in the Query Editor and tried typing this

 

SELECT ID FROM Fruit__c 

and it executes.

 

But If I add "ALL ROWS" at the end, i.e.

SELECT ID FROM Fruit__c ALL ROWS

 

then I got

"Unknown error parsing query" error.

 

If I write this in an Apex code, like this

 

List<Fruit__c> fruits = [SELECT ID FROM Fruit__c ALL ROWS];

 

then the line actually executes successfully.

 

It seems weird to me that "ALL ROWS" keyword cannot be used inside the Query Editor.  Wondering if anyone has encountered that?

 

Thanks

King

sfdcfoxsfdcfox
The query editor doesn't use Apex Code-- it executes directly as a REST query API call. There's no way to query deleted records from the developer console's query editor at this point. Use the Apex Data Loader (Export All), or the workbench (workbench.developerforce.com).
Groundwire ConsultingGroundwire Consulting

HI,

 

SOQL statements can use the ALL ROWS keywords to query all records in an organization, including deleted records and archived activities. 

For example:

 

 [SELECT COUNT() FROM Contact WHERE AccountId = a.Id ALL ROWS]);

 

You can use ALL ROWS to query records in your organization's Recycle Bin. You cannot use the ALL ROWS keywords with the FOR UPDATE keywords.

 

and if you have any more questions please feel to contact me on support@groundwireconsulting.com

sfdcfoxsfdcfox
You can't use that in Developer Console's Query Editor. ALL ROWS and FOR UPDATE are Apex Code only SOQL features.
A AdityaA Aditya
Hi, can anyone explains me how to use ALL ROWS in query, because im getting an error MAL-FORMED Query.
My requirement is to now the deleted records using query
narsavagepnarsavagep

Added Idea for allowing ALL ROWS in Developer Console: https://success.salesforce.com/ideaView?id=08730000000DpgiAAC
Justin J. YueJustin J. Yue
You cannot do it directly within the Query Editor of developer console but there's a workaround for you to test your query in Developer Console. The workaround is to use the "Execute Anonymous Window".

1. Open "Enter Apex Code" window by selecting "Debug" -> "Open Execute Anonymous Window" menu
2. Enter your query there and output the result to the log. Check the "Open Log" checkbox. 

List<Fruit__c> fruits = [SELECT ID FROM Fruit__c ALL ROWS];
System.debug(JSON.serialize(fruits);

3. Click "Execute" to run your code and check the log file for your result.

 
Soumya BeheraSoumya Behera
Thank you Just In You. Your solution worked . Its helped me to find the solution quickly.
SWATI ADMINSWATI ADMIN
select id,isDeleted from account where isDeleted = true

this one is working in query editer...
SalesForce Admin 355SalesForce Admin 355

"just in you"'s  workaround works  

 

David Roberts 4David Roberts 4
Here's a similar piece of code to find archived events. Adjust the query parameters to suit.

integer iCount = 0;
event[] queriedObjects =
[SELECT Id,isarchived,activitydate,subject from Event  where (activitydate = 2017-01-04 and isarchived = true and isdeleted = false) ALL ROWS];
    
    for (event e : queriedObjects) {
        iCount = iCount + 1;
        system.debug('event = '+e.isarchived + '--' + e.activitydate + '--' + e.subject);
        
    }//next
    system.debug('final count = '+iCount);
    
Jason Mower 11Jason Mower 11
The easy answer is to run your query in Workbench and choose the 'Include' option under 'Deleted and archived records'. 
David Roberts 4David Roberts 4
Hi Jason, Yes, it doesn’t work in the query editor. However, it does work in the execute anonymous window. You need to be slightly codey. List lstFruit = [SELECT ID FROM Fruit__c ALL ROWS]; You’ll then need to list the results possibly in chunks if there are lots (and hope you haven’t exceeded your governor limits). integer iCount = 0; for ( Fruit__c aFruit : lstFruits ) { iCount +=1; system.debug(iCount+' '+aFruit.Id); } Regards, Dave.