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
fiona gentryfiona gentry 

How to Write SOQL for 2 custom objects who have same fields Level_1__c, Level_2__c,Level_3__c

Dear gurus,

I have custom object1 named Case_Type_Data__c with Level_1__c, Level_2__c,Level_3__c
and i have another custom object2 named  ERT_Case_Type__c with same fields Level_1__c, Level_2__c,Level_3__c

A batch copied data from ERT_Case_Type__c to Case_Type_Data__c

Now i try to write SOQL in workbench to find out which records didnt get copied
  
Select Level_1__c,Level_2__c,Level_3__c From Case_Type_Data__c Where Level_3__c NOT IN (Select Level_3__c From ERT_Case_Type__c)

Workbench throws error 
MALFORMED_QUERY: semi join sub selects can only query id fields, cannot use: 'Level_3__c'
Please note Id of ERT_Case_Type__c and Id of Case_Type_Data__c doesnt matches hence cant use Id

Please provide me a Join SOQL to get records which didn't got copied from ERT_Case_Type__c to Case_Type_Data__c
Regards
Fiona
 
Best Answer chosen by fiona gentry
fiona gentryfiona gentry
select case__c, createddate, level_1__c, level_2__C, lEVEL_3__C from ert_case_type__c where createddate >= 2020-11-09T00:30:26.467-08:00 and case__c NOT IN (select case__C from case_type__C) order by createddate

All Answers

AbhishekAbhishek (Salesforce Developers) 
You cannot perform arbitrary joins in SOQL. If Level_3__c is not an Id field, you cannot perform a join across these objects, as the error message states.


If this field is, say, a text field, you'd need to either extract the data from Salesforce and analyze it locally, or write Apex code to perform queries against the two objects separately and draw out the data you need. Given your statement that you initially used Batch Apex, the latter approach is likely infeasible due to data volume.


Let me know if it helps you and close your query by marking it as solved so that it can help others in the future.

Thanks.
Edwards HelenEdwards Helen
Facing same problem if you find any solution for this please help me also epayitonline (https://www.epayitonline.us/)
fiona gentryfiona gentry
select case__c, createddate, level_1__c, level_2__C, lEVEL_3__C from ert_case_type__c where createddate >= 2020-11-09T00:30:26.467-08:00 and case__c NOT IN (select case__C from case_type__C) order by createddate
This was selected as the best answer