I need to alias some of our SF fields in a query before I send it out via API. Since alias is not supported in SOQL I have read that a workaround could be to use the format() function whichs allows to us an alias. But if I write someting like:
SELECT format(myField) as Name FROM Table
the alias is not being returned. myField might be an nvarchar or an int.
Anyone who can help me get this working?
This answer assumes that you are working in Apex (as opposed to some other language).
Field aliasing is not something that SOQL is generally capable of. When we can alias a field, the as keyword is not used. The FORMAT() function only states that it works on date/datetime, number, and currency fields, though it does also appear to work on other field types as well (I've tried a picklist, a text field, an autonumber field, and a formula field returning type Text).
Fields aliased in this way do not appear in the results of a query when you use the "Query Editor" tab in the developer console, but they will show up if you system.debug() the results of such a query through anonymous apex.
Accessing the aliased field in apex needs to be done through SObject's get() method (unless you're aliasing to an existing field API name on your object, which is not something I'd generally recommend). So given your example MySObject rec = [SELECT FORMAT(myField__c) Identifier FROM MySObject]; you could access the aliased field using rec.get('Identifier');
When using undocumented features (like the ability of FORMAT() to work on other field types), there is a risk that Salesforce could make a change that causes any non-documented usage to no longer work. So I think the generally preferred approach here would be to create a wrapper class to ensure that your data is accessible via the identifiers you want/need.
Example:
public class Wrapper {
String myAlias;
}
MySObject record = [SELECT Id, Field_To_Alias__c FROM MySObject LIMIT 1];
Wrapper wrapped = new Wrapper();
wrapped.myAlias = record.Field_To_Alias__c;
