function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Proposal ButtonProposal Button 

SFA SQL query

In the SQL that SFA uses, what is the function name that converts the data of a field to upper case?  In Oracle SQL the name is UPPER.  The SFA SQL analyzer says this is an invalid aggregate funtion.  I need to know this so I can make the name search case insensitive. I tried using UCASE,UPPER,InUPPER but it is throwing the same error. I mentioned the error in red. please help me out on this

 

Here is the SQL I sent
SELECT w.SF_Order_Number__c,      
       w.Order_Status__c,      
       w.Name,      
       w.Email__c,      
       w.CreatedDate,      
       w.Account__c,      
       w.Account__r.name,      
       w.Main_BTN__c,      
       w.Account_Billing_State__c,      
       w.Account_Billing_City__c 
  FROM WINORDER__c w
 WHERE (InUpper(w.Account__r.name) LIKE '%WRC%SENIOR%SUPPORT%SERVICES%'      
           AND w.Account_Billing_State__c = 'PA')   
    OR w.Main_BTN__c = '(814) 849-5913'
 
Here is the reply I received
<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sf="urn:fault.enterprise.soap.sforce.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <soapenv:Body>
  <soapenv:Fault>
   <faultcode>sf:MALFORMED_QUERY</faultcode>
   <faultstring>MALFORMED_QUERY: Invalid aggregate function: InUpper</faultstring>
   <detail>
    <sf:MalformedQueryFault xsi:type="sf:MalformedQueryFault">
     <sf:exceptionCode>MALFORMED_QUERY</sf:exceptionCode>
     <sf:exceptionMessage>Invalid aggregate function: InUpper</sf:exceptionMessage>
     <sf:row>-1</sf:row>
     <sf:column>-1</sf:column>
    </sf:MalformedQueryFault>
   </detail>
  </soapenv:Fault>
 </soapenv:Body>
</soapenv:Envelope>
Thanks
Sayana

 

 

SuperfellSuperfell

There are no functions in SOQL for string case. String comparisions should already be case insensitive
Proposal ButtonProposal Button

Thanks Simon for the reply. In this case as a salesforce developer we provided the SQL query and there is a middleware who is creating a webservice and calling out salesforce using name,city and state. they want to make that name as case insensitive so they were finding a toughtime in providing a right syntax.
SuperfellSuperfell

As i mentioned, string comparisons in SOQL are case insensitive
Proposal ButtonProposal Button

Thanks Simon for the reply. In this case as a salesforce developer we provided the SQL query and there is a middleware who is creating a webservice and calling out salesforce using name,city and state. they want to make that name as case insensitive so they were finding a toughtime in providing a right syntax. I am not sure in which environment there are creating a webservice. So do you say that there is no need to enter an syntax like Upper,Ucase,Inupper?
Proposal ButtonProposal Button

i am sorry just saw your recent reply..so do you mean that there is no need of any syntax to keep before the account name?

 

Thanks

Sayana
SuperfellSuperfell

Right, you don't need to do anything, e.g. this query 

select id, name from account where name='foo'

 

will return rows where name is foo, name is FOO or name is Foo (etc)
Proposal ButtonProposal Button

Thankyou very much simon its working great. they tried with lowercase,uppercase and mixed case its working great. appreciate for your time and help

 

Thanks

Sayana