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))

3 Answers 3

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
    Commented Mar 30, 2016 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 Commented Mar 31, 2016 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
    Commented Mar 31, 2016 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. Commented Mar 31, 2016 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
    Commented Mar 31, 2016 at 13:21
  • Thanks ... I have done this by using the sets in separate queries. Commented Apr 1, 2016 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);

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .