3

I am having a hard time with a SOQL query. I want some data from a table using two like conditions on Name. How can I correct my query so that it is a single query rather than two separate queries?

  Select Id, Name , Isactive, sobjecttype from recordtype
   where (select Name from recordtype where 
              sobjecttype = 'Insurance_Product__c' and isactive = true ) 
       = (select Name from recordtype where sobjecttype = 
         'Policy__c' and isactive = false))
2

First of all, seems like a very strange query, but this should do it:

 SELECT Id, Name , Isactive, sobjecttype FROM recordtype
 WHERE Name IN (select Name from recordtype where sobjecttype = 
         'Policy__c' and isactive = false) 
 AND 
       Name IN (select Name from recordtype where sobjecttype = 
         'Insurance_Product__c' and isactive = true )
3
  • Hmm..semi-joins on non ID fields are not permitted; see developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/…
    – cropredy
    Mar 30 '16 at 16:55
  • @Nicolas Thanks I have tried with your query as I do also think it as the best solution but trying this query returns me an error as: - ERROR at Row:2:Column:34 The inner and outer selects should not be on the same object type on recordtype object Mar 31 '16 at 6:18
  • I'm afraid then your only other option is to have 2 queries and compare the results of those 2 queries to get the outcome you desire. That said, it might be more interesting to tell us what you are actually trying to achieve with this query so we can see whether there is another option for this (not using code).
    – nicolas
    Mar 31 '16 at 7:19
0

You should just be able to setup your query like:

SELECT Id, Name , Isactive, SobjectType from RecordType
        WHERE (SobjectType = 'Insurance_Product__c' AND IsActive = true) OR
              (SobjectType = 'Policy__c' AND IsActive = false)  

which will return both Active Insurance Product Record Types and InActive Policy Record types.

Note: otherwise you will definitely need to do a separate query as you can not do self inner joins as stated in the docs here:

You cannot query on the same object in a subquery as in the main query.

3
  • thanks for the try.. but or operator will not return me the desired result as it will return me both Active Insurance Product Record Types and InActive Policy Record types. But I need names that are having the active insurance types and inactive policy type in a single value. Mar 31 '16 at 6:12
  • @HarishChauhan it's not going to be possible in a single query then you will need to create a set of names from the first query and use that to filter the second query.
    – Jenny B
    Mar 31 '16 at 13:21
  • Thanks ... I have done this by using the sets in separate queries. Apr 1 '16 at 8:00
0

using the same object in the inner query is not possible in the Apex so I have done it as:

oppListActive= [Select Id, Name , Isactive, sobjecttype  from recordtype where sobjecttype = 'Insurance_Product__c' and isactive = true ];

          set<String> activeSet = new Set<String>();
             for(recordtype r : oppListActive)
                   {
                       activeSet.add(r.Name);
                   }
           System.debug('activeSet== ' + activeSet);
           oppListInactive=  [Select Id, Name , Isactive, sobjecttype  from recordtype where sobjecttype = 'Policy__c' and isactive = false and Name IN :activeSet ];
          System.debug('oppListInactive== ' + oppListInactive);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.