2

I would like to use a static query that pulls out data using a LIKE operator which includes null values. For example, for the following data:

================================
|   ID   |   CategoryName__c   |
================================
|   01   |   "category one"    |
|   02   |   "category two"    |
|   03   |   "category two"    |
|   04   |       (null)        |
================================

I would like to be able to, in a single SOQL static query, pull out all four record regardless of their value. For example:

[SELCT ID, Name FROM ExampleTable__c WHERE CategoryName__c LIKE :magic]

Where magic is some string. I have tried different variations of % for this magic value, but it does not pick up null values. The purpose of this is to be able to filter on one category, or not filter on any (depending upon the user input).

I could have an if statement and two SOQL statements, but it would be so much nicer to just have one statement.

If I were not being restricted to static queries, I would solve my problem easily like this:

String sqlStatement = 'SELCT ID, Name FROM ExampleTable__c ';
if(singleCategoryOnly){
    sqlStatement += 'CategoryName__c LIKE ' + userSelectedCategory;
}
//execute statement....
Improve this question
4
  • have you tried list<exampletable__c> = database.query(yourquerystring) ;
    – Sam
    Commented Sep 9, 2013 at 4:44
  • @theGreatDanton; as my question states, I want to use a STATIC query...
    – lindon fox
    Commented Sep 9, 2013 at 4:48
  • in the second part of your question you are constructing a string which you want to execute, right??
    – Sam
    Commented Sep 9, 2013 at 4:53
  • @theGreatDanton, right: and the ellipsis represents everything else (including your code). I know how to do that. My question is about STATIC queries. What you are suggesting is not a STATIC query. The last part was to say that I know how to do it if I did not have to use static queries (in order to avoid people giving such an answer...).
    – lindon fox
    Commented Sep 9, 2013 at 5:10

2 Answers 2

Reset to default
1

SELECT ... WHERE CategoryName__c LIKE :key OR CategoryName__c = NULL

Remember to group using parenthesis if you mix AND and OR keywords.

Improve this answer
1
  • Sorry if my question was not clear enough. This answer will not fulfil my requirements. I want to be able to filter on either a specific category (records with that category only), or none at all (all records).
    – lindon fox
    Commented Sep 9, 2013 at 4:47
1

OK, I have figured out a solution. Not as elegant as I was hoping for, but it works. If you can think of a more elegant solution, then I will still award the answer to you.

String categoryOne;
String categoryTwo;

if(userSelectedACategory){
    categoryOne = categoryTwo = userSelection;
}
else{
    categoryOne = '%';//for all categories
    categoryTwo = null;//for records not set to a category
}
[SELECT ID FROM CategoryName__c WHERE (CategoryName__c = :categoryOne OR CategoryName__c = :categoryTwo) AND ... ];

Note, it is possible to extend this idea to dates and ranges of dates.

Improve this answer

You must log in to answer this question.

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