3

Following query returns an error:

Query:

SELECT Id, FirstName, LastName, OwnerId, PersonEmail
FROM Account 
WHERE lower(PersonEmail) = lower('abc.DEF@org.cOM')

API Response:

success: false
    result: Dictionary
    error: IntegrationError
        title: "The JSON body contains an error"
        message: "Salesforce returned the following error code: MALFORMED_QUERY"
        detail: "
'%test%' and lower(PersonEmail) = lower('abc.DEF@org.cOM')
                                 ^
ERROR at Row:4:Column:54
Bind variables only allowed in Apex code"

Can't we use SQL functions in SOQL?

Rakmo
  • 436
  • 2
  • 7
  • 18

2 Answers2

3

You do not need to change the text to lower case:

Comparisons on strings are case-sensitive for unique case-sensitive fields and case-insensitive for all other fields

EDIT: to put it another way, only specific fields are uniquely marked to be case sensitive. The rest aren't. Also, emails are stored as all lowercase by default. Also, try the LIKE comparison, which (I believe) is case insensitive even for case sensitive fields.

Abraham Labkovsky
  • 1,771
  • 6
  • 12
  • Exactly, as the string comparison is case sensitive, I need to avoid case sensitivity and just compare the strings in lower or upper case. For eg. An account record with email "abc.def@org.com" is available, but while searching user entered the "abc@DEF@ORG.com". In this case, the expectation is to get the account record from SFDC. – Rakmo Jul 28 '20 at 17:27
  • Did you try removing your text function? The comparison is supposed to be case insensitive. The docs say they are case sensitive _only_ for some specific fields. – Abraham Labkovsky Jul 28 '20 at 20:08
  • By default, emails are lowered to lowercase in SF anyways. Regardless of how they are entered. Also, I don't recognize the field `PersonEmail` on `Account`. If it is a custom field, ensure you are using the correct API name. – Abraham Labkovsky Jul 28 '20 at 20:12
  • 1
    @AbrahamLabkovsky they have person accounts enabled. All Contact fields that silently start to appear on Account get `Person...` prefix – eyescream Jul 28 '20 at 21:01
2

Can't we use SQL functions in SOQL?

No, you can't. SOQL is a Salesforce-specific dialect. Here's a decent list of what you can use: https://salesforce.stackexchange.com/questions/166372/all-functions-available-in-soql. And any comparison you make must be in field operator value style. You can't compare field value with another field's value (apart from primary / foreign keys... you could write formulas for that though). And you can't do "clever" weird queries WHERE 1=1 AND...

This is not too different from other SQL dialects really? To me SQL Server's date format "112" is equally strange as to you lack of LOWER. If you really want to have a lowercase value returned/displayed in UI you can make a formula field in SF (bit like adding a column to materialized view?) - but comparisons on it will still be case-insensitive and probably slower, full table search to run ultimately useless function instead of using indexes.

SOQL is case insensitive on database level (I believe it's called collation?). Any SELECTs you make will return hits ignoring case so you don't have to explicitly call LOWER() There are some exceptions to this but PersonEmail is not one of them:

  • If you have custom field marked as unique case sensitive (you could ask admin to build an automationt hat copies value from PersonEmail to such custom field but i don't think there's a point)
  • If you use Platform Encryption (a.k.a. Salesforce Shield) and used Deterministic Encryption method with case-sensitive option.
eyescream
  • 18,088
  • 2
  • 34
  • 46