2

In SQL Server, we had to check if the parameter contains data in order to count it in the query.

The working code :

[Select ObjectId, Territory2Id 
From ObjectTerritory2Association
Where ObjectId IN: setObjectIds
AND Territory2Id IN: setTerritoryIds]

What I would like to have

[Select ObjectId, Territory2Id 
From ObjectTerritory2Association 
Where (ObjectId IN: setObjectIds AND setObjectIds != null) 
AND Territory2Id IN: setTerritoryIds];

How do we do that in SOQL ?

2 Answers 2

1

I ended up doing this :

    // Build the query
    string queryOTA = 'Select ObjectId, Territory2Id From ObjectTerritory2Association ';

    if(setObjectIds != null && setObjectIds.size()>0 && setTerritoryIds != null && setTerritoryIds.size()>0)
    {
        queryOTA += 'Where Territory2Id IN \'' + setTerritoryIds + ' AND ObjectId IN \'' + setObjectIds;
    }
    else
    if(setObjectIds != null && setObjectIds.size()>0)
    {
        queryOTA += 'Where ObjectId IN \'' + setObjectIds;
    }
    else
    if(setTerritoryIds != null && setTerritoryIds.size()>0)
    {
        queryOTA += 'Where Territory2Id IN \'' + setTerritoryIds;
    }


    //Get ObjectTerritory2Association list
    List<ObjectTerritory2Association> listOT2A = Database.query(queryOTA);
0
0

In SOQL this is not possible to check null of variables in query. So you need to do this

if(setObjectIds != null)
{
[Select ObjectId, Territory2Id  From ObjectTerritory2Association Where ObjectId IN: setObjectIds AND Territory2Id IN: setTerritoryIds]
}

Or

if(setObjectIds == null)
   setObjectIds  = new Set<Id>(); //if your set data type is different use that

 [Select ObjectId, Territory2Id  From ObjectTerritory2Association Where ObjectId IN: setObjectIds AND Territory2Id IN: setTerritoryIds]

You can also do something like

// Build the query
    string queryOTA = 'Select ObjectId, Territory2Id From ObjectTerritory2Association ';

    if(setObjectIds != null && setObjectIds.size()>0 && setTerritoryIds != null && setTerritoryIds.size()>0)
    {
        queryOTA += 'Where Territory2Id IN : setTerritoryIds  AND ObjectId IN:  setObjectIds';
    }
    else
    if(setObjectIds != null && setObjectIds.size()>0)
    {
        queryOTA += 'Where ObjectId IN : setObjectIds';
    }
    else
    if(setTerritoryIds != null && setTerritoryIds.size()>0)
    {
        queryOTA += 'Where Territory2Id IN : setTerritoryIds';
    }


    //Get ObjectTerritory2Association list
    List<ObjectTerritory2Association> listOT2A = Database.query(queryOTA);

You must log in to answer this question.

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